1 / 36

B-Tree Indexing

B-Tree Indexing. emp table DDL. CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0)

cecilia-porter
Download Presentation

B-Tree Indexing

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. B-Tree Indexing

  2. Data Management

  3. Data Management

  4. Data Management

  5. emp table DDL CREATE TABLE "SCOTT"."EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0) ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" Data Management

  6. emp table EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7934 MILLER CLERK 7782 23-JAN-82 1300 10 14 rows selected. Data Management

  7. row no : 1 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 0 row no : 2 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 1 row no : 3 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 2 row no : 4 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 3 row no : 5 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 4 row no : 6 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 5 row no : 7 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 6 row no : 8 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 7 row no : 9 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 8 row no : 10 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 9 row no : 11 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 10 row no : 12 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 11 row no : 13 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 12 row no : 14 file : C:\ORACLE\PRODUCT\10.1.0\ORADATA\ORCL\USERS01.DBF block no: 6615 slot no : 13 PL/SQL procedure successfully completed. set serveroutput on size 1000000 format wrapped declare r rowid; i number := 1; v_filenamedba_data_files.file_name%type; begin for p in ( select rowid from emp ) loop select file_name into v_filename from dba_data_files where file_id = dbms_rowid.rowid_relative_fno(p.rowid); dbms_output.put_line('row no : ' || i ); dbms_output.put_line(' file : ' || v_filename); dbms_output.put_line(' block no: ' || dbms_rowid.rowid_block_number(p.rowid)); dbms_output.put_line(' slot no : ' || dbms_rowid.rowid_row_number(p.rowid)); dbms_output.put_line(''); i := i+1; end loop; end; / emp table This needs to be run with DBA privileges or the dba_data_files table needs to have privileges granted to the “connect” role. Data Management

  8. Free Integrity Integrity Integrity Integrity Transaction Transaction Transaction Transaction Item Directory Item Directory Item Directory Item Directory 7782^CLARK^MANAGER^7839^09-JUN-81^2450^ ^10 7499^ALLEN^SALESMAN^7698^20-FEB-81^1600^30 0^30 7369^SMITH^CLERK^7902^17-DEC-80^800^^20 79 Free 02^FORD^ANALYST^7566^03-DEC-81^3000^^20 check 7698^BL AKE^MANAGER^7839^01-MAY-81^2850^^30 7844^TU RNER^SALESMAN^7698^08-SEP-81^1500^^0^30 7788^ SCOTT^ANALYST^7566^09-DEC-82^3000^^20 7521^WA Free Free RD^SALESMAN^7698^22-FEB-81^1250^500^30 check 7839^KING^PRESIDENT^^17-NOV-81^ 5000^^10 7876^ADAMS^CLERK^7788^12-JAN-83^1100^ 76 ^20 7566^JONES^MANAGER^7839^02-APR-81^2975^^20 54^MARTIN^SALESMAN^7698^28-SEP-81^1250^1400^30 7900^JAMES^CLERK^7698^03-DEC-81^950^^30 check check 7934^MILLER^CLERK^7782^23-JAN-82^1300^^10 Block 1 Block 2 Simplified Oracle Data Blocks for scott.emp Block 3 Block 4 Data Management

  9. B+-tree Indexing • What does • create index empno_index on emp(empno) • actually do? Data Management

  10. Simplified B+-tree for an index on empno Step 1 Insert 7782:1 Step 2 Insert 7499:1 Pointer to data block containing index key value Useful querys: SELECT dbms_rowid.rowid_block_number(rowid) FROM dept SELECT dbms_rowid.rowid_row_number(rowid) FROM dept Index key value Step 3 Insert 7369:1 Data Management

  11. Simplified B+-tree for an index on empno Step 4 Insert 7902:1 Step 5 Insert 7698:2 Data Management

  12. Simplified B+-tree for an index on empno Step 6 Insert 7844:2 Step 7 Insert 7788:2 Data Management

  13. Simplified B+-tree for an index on empno Step 8 Insert 7521:2 Step 9 Insert 7839:3 New Data Management

  14. Simplified B+-tree for an index on empno Step 10 Insert 7876:3 Data Management

  15. Simplified B+-tree for an index on empno Step 11 Insert 7566:3 Data Management

  16. Simplified B+-tree for an index on empno Step 12 Insert 7900:3 Data Management

  17. Simplified B+-tree for an index on empno Step 13 Insert 7654:4 Data Management

  18. Empno_index DDL CREATE INDEX "SCOTT"."EMPNO_INDEX" ON "SCOTT"."EMP" ("EMPNO") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "SYSTEM" Data Management

  19. root root Simplified B+-tree for an index on empno Step 1 Insert 7782:1 Block 1 Integrity Transaction Item Directory 7782:1 check Step 2 Insert 7499:1 Block 1 Integrity Transaction Item Directory 7499:1 7782:1 check Data Management

  20. root Simplified B+-tree for an index on empno Block 1 Integrity Transaction Item Directory 7369:1 2 check Step 3 Insert 7369:1 Pointer Block 2 Integrity Transaction Item Directory Index Blocks 7499:1 7782:1 check Block 3 Integrity Transaction Item Directory 7499:1:2 check Data Management

  21. root Simplified B+-tree for an index on empno Step 4 Insert 7902:1 Block 1 Integrity Transaction Item Directory 7369:1 2 check Block 2 Integrity Transaction Item Directory 7499:1 4 check Block 3 Integrity Transaction Item Directory 7499:1:2 7782:2:4 check Block 4 Integrity Transaction Item Directory 7782:1 7902:1 check Data Management

  22. root Simplified B+-tree for an index on empno Step 5 Insert 7698:2 Block 1 Integrity Transaction Item Directory 7369:1 2 check Block 2 Integrity Transaction Item Directory 7499:1 7698:2 4 check Block 3 Integrity Transaction Item Directory 7499:1:2 7782:2:4 check Block 4 Integrity Transaction Item Directory 7782:1 7902:1 check Data Management

  23. root Simplified B+-tree for an index on empno Step 6 Insert 7844:2 Block 1 Integrity Transaction Item Directory 7369:1 2 check Block 3 Integrity Transaction Item Directory Block 2 Integrity Transaction Item Directory 7499:1:2 7499:1 7698:2 4 check 7782:2:4 7844:4:5 check Block 4 Block 5 Integrity Transaction Item Directory Integrity Transaction Item Directory 7782:1 5 check 7844:2 7902:1 check Data Management

  24. root Simplified B+-tree for an index on empno Step 7 Insert 7788:2 Block 1 Integrity Transaction Item Directory 7369:1 2 check Block 3 Integrity Transaction Item Directory Block 2 Integrity Transaction Item Directory 7499:1:2 7499:1 7698:2 4 check 7782:2:4 7844:4:5 check Block 4 Block 5 Integrity Transaction Item Directory Integrity Transaction Item Directory 7782:1 7788:2 5 check 7844:2 7902:1 check Data Management

  25. root Simplified B+-tree for an index on empno Step 8 Insert 7521:2 Block 1 Integrity Transaction Item Directory 7369:1 2 check Block 3 Integrity Transaction Item Directory Block 2 Integrity Transaction Item Directory 7499:1:2 7521:2:6 7499:1 6 check 7782:6:4 7844:4:5: check Block 5 Integrity Transaction Item Directory Block 4 7844:2 7902:1 check Integrity Transaction Item Directory Block 6 7782:1 7788:2 5 check Integrity Transaction Item Directory 7521:2 7698:2 4 check Data Management

  26. root Simplified B+-tree for an index on empno Step 9 Insert 7839:3 New 8 9 3 1 2 6 4 5 7 Block 3 Interior Nodes Integrity Transaction Item Directory Block 8 7499:1:2 7521:2:6 check Integrity Transaction Item Directory Block 9 Integrity Transaction Item Directory 7782:3:9: check 7788:4:5 7844:5:7 check Data Management

  27. Simplified B+-tree for an index on empno Etc. Data Management

  28. To find out what indexes exist select * from user_indexes Data Management

  29. SGA Buffer Cache (Default Buffer Pool) Log Buffer Library Cache Large Pool Keep Buffer Pool Dictionary Cache Sort Area Recycle Buffer Pool SQL Area Oracle System Global Area (SGA) Operating System File System Cache Indexes Logs Data Data Management

  30. Moving Blocks from disk to memory * These blocks numbers are based on the blocks shown at step 9 which are not necessarily what the final block number would be if the example had be completed through step 14. Data Management

  31. B+-tree Indexing Try doing step 14 for the empno_index B+-tree. (Step 13 was on page 17 of these notes) Data Management

  32. Other uses of B-Trees – Store JSON j = { "id" : 1, "name" : { "first" : "John", "last" :"Backus" }, "contribs" : [ "Fortran", "ALGOL", "Backus-Naur Form”, "FP" ], "awards" : [ { "award" : "W.W. McDowell Award", "year" : 1967, "by" : "IEEE Computer Society” }, { "award" : "Draper Prize", "year" : 1993, "by" : "National Academy of Engineering” } ] } Data Management

  33. Other uses of B-Trees – Store JSON continued def flatten(structure, key="", path="", flattened=None): # pseudo BSON if flattened is None: flattened = {} if type(structure) not in(dict, list): flattened[((path + "_") if path else "") + key] = structure elif isinstance(structure, list): for i, item in enumerate(structure): flatten(item, "%d" % i, path + "_" + key, flattened) else: for new_key, value in structure.items(): flatten(value, new_key, path + "_" + key, flattened) return flattened f = flatten(j, path="GUID1") for key in sorted(f.keys()) : print key, f[key] Data Management

  34. Other uses of B-Trees – Store JSON continued GUID1__awards_0_award W.W. McDowell Award GUID1__awards_0_by IEEE Computer Society GUID1__awards_0_year 1967 GUID1__awards_1_award Draper Prize GUID1__awards_1_by National Academy of Engineering GUID1__awards_1_year 1993 GUID1__contribs_0 Fortran GUID1__contribs_1 ALGOL GUID1__contribs_2 Backus-Naur Form GUID1__contribs_3 FP GUID1__id 1 GUID1__name_first John GUID1__name_last Backus j = { "id" : 1, "name" : { "first" : "John", "last" :"Backus" }, "contribs" : [ "Fortran", "ALGOL", "Backus-Naur Form”, "FP" ], "awards" : [ { "award" : "W.W. McDowell Award", "year" : 1967, "by" : "IEEE Computer Society” }, { "award" : "Draper Prize", "year" : 1993, "by" : "National Academy of Engineering” } ] } Data Management

  35. Other uses of B-Trees – Store JSON continued • Build 13 separate B-trees (MongoDB Collection): • awards_0_award • GUID1: W.W. McDowell Award • GUID2: W.W. McDowell Award • awards_0_by • GUID1: IEEE Computer Society • awards_0_year • GUID1: 1967 • awards_1_award • GUID1: Draper Prize • awards_1_by • GUID1: National Academy of Engineering • awards_1_year • GUID1 1993 • contribs_0 • GUID1: Fortran • contribs_1 • GUID1: ALGOL • contribs_2 • GUID1: Backus-Naur Form • contribs_3 • GUID1: FP • id • GUID1: 1 • name_first • GUID1: John • name_last • GUID1: Backus j = { "id" : 1, "name" : { "first" : "John", "last" :"Backus" }, "contribs" : [ "Fortran", "ALGOL", "Backus-Naur Form”, "FP" ], "awards" : [ { "award" : "W.W. McDowell Award", "year" : 1967, "by" : "IEEE Computer Society” }, { "award" : "Draper Prize", "year" : 1993, "by" : "National Academy of Engineering” } ] } Data Management

  36. Other uses of B-Trees – Store JSON continued • Build 13 separate B-trees (MongoDB Collection): • awards_0_award • GUID1: W.W. McDowell Award • awards_0_by • GUID1: IEEE Computer Society • awards_0_year • GUID1: 1967 • awards_1_award • GUID1: Draper Prize • awards_1_by • GUID1: National Academy of Engineering • awards_1_year • GUID1 1993 • contribs_0 • GUID1: Fortran • contribs_1 • GUID1: ALGOL • contribs_2 • GUID1: Backus-Naur Form • contribs_3 • GUID1: FP • id • GUID1: 1 • name_first • GUID1: John • name_last • GUID1: Backus Queries // MongoDB Query Operators: // $all, $exists, $mod, $ne, $in, $nin, $nor, $or, // $size, $type, $lt, $lte, $gt, $gte // find contacts with any Fortran contributions > db.addressBook.find({contribs: {$exists: true}}) // find entries matching a regular expression > db.addressBook.find( {name_last: /^Ba*/i } ) // count entries with “John” > db.addressBook.find( {name_first: ‘John’} ).count() Each of these queries would fetch GUID1 that can then be used to return the JSON Document. Data Management

More Related