1 / 21

Tipos de Segmentos

Tipos de Segmentos. B-Tree Index. Index entry. Root. Branch. Index entry header. Key column length. Leaf. Key column value. ROWID. Almacenamiento en los índices . PCTFREE DB_BLOCK_SIZE-OVERHEAD-entries* ENTRY_ OVERHEAD-ENTRIES(directorio valores)-ROWID. Reescritura de Consultas.

efrem
Download Presentation

Tipos de Segmentos

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Tipos de Segmentos

  2. B-Tree Index Index entry Root Branch Index entry header Key column length Leaf Key column value ROWID

  3. Almacenamiento en los índices • PCTFREE • DB_BLOCK_SIZE-OVERHEAD-entries* ENTRY_ OVERHEAD-ENTRIES(directorio valores)-ROWID

  4. Reescritura de Consultas

  5. Function-Based Indexes SQL> CREATE INDEX FBI_UPPER_LASTNAME 2 ON CUSTOMERS(upper(cust_last_name)); SQL> ALTER SESSION 2 SET QUERY_REWRITE_ENABLED = TRUE; SQL> ALTER SESSION 2 SET QUERY_REWRITE_ENABLED = enforced | trusted | stale_tolerated; SQL> SELECT * 2 FROM customers 3 WHERE UPPER(cust_last_name) = 'SMITH';

  6. 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

  7. Índices y nulos • Oracle ignora los registro donde todas las entradas son nulos. • CREATE INDEX IDX1 ON EMPLOYEE(NVL(ename,’null’)

  8. Ejemplo Bitmap- Tabla base

  9. Ejemplo Bitmap (Bitmap Generado)

  10. Ejemplo Consulta sobre Bitmap SELECT COUNT(*) FROM CUSTOMER WHERE MARITAL_STATUS = 'married' AND REGION IN ('central','west');

  11. start ROWID end ROWID key 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> Bitmap Indexes File 3 Table Block 10 Block 11 Block 12 Index

  12. 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;

  13. B-tree Suitable for high-cardinality columns Updates on keys relativelyinexpensive Inefficient for queries using OR predicatesUseful for OLTP Bitmap Suitable for low-cardinality columns Updates to key columns veryexpensive Efficient for queries using OR predicatesUseful for data warehousing Comparing B-Tree andBitmap Indexes

  14. COMPUTE STATISTICS • CREATE INDEX ord_customer_ix_demo ON orders(customer_id, sales_rep_id) COMPUTE STATISTICS;

  15. Í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);

  16. Í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));

  17. Índices globales no particionados sobre tablas particionadas CREATE INDEX employees_global_idx ON employees(employee_id);

  18. Hash-Partitioned Global Indexes: Overview 10g Range-partitioned global index … … 900, 901, 1000, 1001, < 1000 < MAXVALUE Hash-partitioned global index … … 900, 1000, 901, 1001,

  19. 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> CREATE INDEX ghoi_ix 2 ON order_items (order_id) GLOBAL 3 PARTITION BY HASH (order_id) 4 PARTITIONS 4 5 STORE IN (tbs_1, tbs_2, tbs_3, tbs_4);

  20. Índices Bipmap para IOT • Tabla de Correspondencia • 9i • Hash, rango • 10g • Lista

  21. 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);

More Related