Transcript Ejemplo Bitmap

Tipos de Segmentos
B-Tree Index
Index entry
Root
Branch
Index entry header
Leaf
Key column length
Key column value
ROWID
Almacenamiento en los índices


PCTFREE
DB_BLOCK_SIZE-OVERHEAD-entries*
ENTRY_ OVERHEAD-ENTRIES(directorio
valores)-ROWID
Reescritura de Consultas
Function-Based Indexes
SQL> CREATE INDEX FBI_UPPER_LASTNAME
2 ON CUSTOMERS(upper(cust_last_name));
SQL>
2
SQL>
2
ALTER SESSION
SET QUERY_REWRITE_ENABLED = TRUE;
ALTER SESSION
SET QUERY_REWRITE_ENABLED = enforced | trusted
| stale_tolerated ;
SQL> SELECT *
2 FROM
customers
3 WHERE UPPER(cust_last_name) = 'SMITH';
Function-Based Indexes: Usage

Function-based indexes:




Materialize computational-intensive
expressions
Facilitate case-insensitive searches
Provide a simple form of data
compression
Can be used for an NLS sort index
Índices y nulos


Oracle ignora los registro donde todas las
entradas son nulos.
CREATE INDEX IDX1 ON
EMPLOYEE(NVL(ename,’null’)
Ejemplo Bitmap- Tabla base
CUSTOMER #
MARITAL_ STATUS
REGION
GENDER
INCOME_ LEVEL
101
single
east
male
bracket_1
102
married
central female
bracket_4
103
married
west
female
bracket_2
104
divorced
west
male
bracket_4
105
single
central female
bracket_2
106
married
central female
bracket_3
Ejemplo Bitmap (Bitmap Generado)
REGION='east'
REGION='central'
REGION='west'
1
0
0
0
1
0
0
0
1
0
0
1
0
1
0
0
1
0
Ejemplo Consulta sobre Bitmap
SELECT COUNT(*)
FROM CUSTOMER
WHERE MARITAL_STATUS = 'married' AND
REGION IN ('central','west');
Bitmap Indexes
Table
File 3
Block 10
Block 11
Block 12
Index
start
end
key ROWID ROWID
bitmap
<Blue, 10.0.3, 12.8.3, 1000100100010010100>
<Green, 10.0.3, 12.8.3, 0001010000100100000>
<Red, 10.0.3, 12.8.3, 0100000011000001001>
<Yellow, 10.0.3, 12.8.3, 0010001000001000010>
Creating Bitmap Indexes
CREATE BITMAP INDEX orders_region_id_idx
ON orders(region_id)
PCTFREE 30
STORAGE(INITIAL 200K NEXT 200K
PCTINCREASE 0 MAXEXTENTS 50)
TABLESPACE indx;
Comparing B-Tree and
Bitmap Indexes
B-tree
Bitmap
Suitable for high-cardinality Suitable for low-cardinality
columns
columns
Updates on keys relatively
inexpensive
Updates to key columns very
expensive
Inefficient for queries
using OR predicates
Efficient for queries
using OR predicates
Useful for OLTP
Useful for data warehousing
COMPUTE STATISTICS

CREATE INDEX ord_customer_ix_demo
ON orders(customer_id, sales_rep_id)
COMPUTE STATISTICS;
Índices particionados localmente sobre
tablas Particionadas
CREATE TABLE employees
(employee_id NUMBER(4) NOT NULL,
last_name VARCHAR2(10),
department_id NUMBER(2))
PARTITION BY RANGE (department_id)
(PARTITION employees_part1 VALUES LESS THAN (11)
TABLESPACE part1,
PARTITION employees_part2 VALUES LESS THAN (21)
TABLESPACE part2,
PARTITION employees_part3 VALUES LESS THAN (31)
TABLESPACE part3);
Índices Particionados Globalmente
sobre Tablas Particionadas
CREATE INDEX employees_global_part_idx ON
employees(employee_id)
GLOBAL PARTITION BY RANGE(employee_id)
(PARTITION p1 VALUES LESS THAN(5000),
PARTITION p2 VALUES LESS THAN(MAXVALUE));
Índices globales no particionados sobre
tablas particionadas
CREATE INDEX employees_global_idx ON
employees(employee_id);
Hash-Partitioned Global
Indexes: Overview 10g
Range-partitioned global index
900, 901, …
< 1000
1000, 1001, …
< MAXVALUE
Hash-partitioned global index
900, 1000, …
901, 1001, …
Creating Hash-Partitioned
Global Indexes
SQL> CREATE INDEX ghoi_ix
2 ON order_items (order_id) GLOBAL
3 PARTITION BY HASH (order_id) (
4
PARTITION p1 TABLESPACE tbs_1,
5
PARTITION p2 TABLESPACE tbs_2,
6
PARTITION p3 TABLESPACE tbs_3,
7
PARTITION p4 TABLESPACE tbs_4);
SQL>
2
3
4
5
CREATE INDEX ghoi_ix
ON order_items (order_id) GLOBAL
PARTITION BY HASH (order_id)
PARTITIONS 4
STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);
Índices Bipmap para IOT

Tabla de Correspondencia
ROWID Físico

9i


Hash, rango
10g

Lista
ROWID Lógico
Creación de una Tabla Particionada
Organizada por Índice
CREATE TABLE sales_range(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE,
PRIMARY KEY(sales_date, salesman_id))
ORGANIZATION INDEX INCLUDING salesman_id
OVERFLOW TABLESPACE tabsp_overflow
PARTITION BY RANGE(sales_date)
(PARTITION sales_jan2000 VALUES LESS
THAN(TO_DATE('02/01/2000','DD/MM/YYYY'))
OVERFLOW TABLESPACE p1_overflow,
PARTITION sales_feb2000 VALUES LESS
THAN(TO_DATE('03/01/2000','DD/MM/YYYY'))
OVERFLOW TABLESPACE p2_overflow,
PARTITION sales_mar2000 VALUES LESS
THAN(TO_DATE('04/01/2000','DD/MM/YYYY'))
OVERFLOW TABLESPACE p3_overflow,
PARTITION sales_apr2000 VALUES LESS
THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
OVERFLOW TABLESPACE p4_overflow);