What a DBA Needs to Know About Oracle’s Bitmap Indexing to Retrieve Data Quickly? Part I Vilin Roufchaie Cingular Wireless [email protected] [email protected] Download slides: www.nocoug.org 11/6/2015
Download ReportTranscript What a DBA Needs to Know About Oracle’s Bitmap Indexing to Retrieve Data Quickly? Part I Vilin Roufchaie Cingular Wireless [email protected] [email protected] Download slides: www.nocoug.org 11/6/2015
What a DBA Needs to Know About Oracle’s Bitmap Indexing to Retrieve Data Quickly? Part I
Vilin Roufchaie Cingular Wireless [email protected]
Download slides: www.nocoug.org
5/1/2020 1
Presentation Assumptions & Prerequisites Familiarity with basic Database and Data Warehouse concepts is required Bitmap and Bit Vector will be used interchangeably 5/1/2020 2
Who Is This Presentation For?
Data warehouse designers & developers
DBAs
Performance DBAs
Capacity Planners
5/1/2020 3
Presentation Summary Part I Overview, Characteristics, Structure & Size of a Bitmap Index Performance Considerations of Bitmap Indexing Logical Layout of Bitmap Indexes Bitmap Index Creation & Storage Issues Query Processing & Bitmap Index Access Paths 5/1/2020 4
Presentation Summary Part II Star Schema, Join, & Transformation Star Optimization, and Transformation CBO Estimation & Query Transformer Enabling Star Transformation Star Transformation Steps 5/1/2020 5
Presentation Summary Part II Joinback Elimination Case Study & Explain Plan Review To “Star Query”? Or To “Star Transform” ?
Conclusions Acknowledgements 5/1/2020 6
Indexing Rules-of-Thumb In building an index know : data selectivity, data distribution, workload, execution plan, proof of utilization.
There is cost & overhead in building, utilizing, tuning, & maintaining indexes of any sort!
What do we expect after paying those cost?
5/1/2020 7
Indexing Rules-of-Thumb Performance! Orders of magnitude in execution speed ? Ideal!!
How about what makes the Business/users happy!
Make sure business expectations are understood & adequate research & tests are done to assess the likelihood of succeeding before committing to something 5/1/2020 8
Indexing Rules-of-Thumb The index must be beneficial to all SQL's impacted by it across the board - Wholly beneficial Try Aggregate/Collective tuning So we want to build few , efficient indexes Demand: Low-overhead indexes, requiring less frequent & fast re/builds, & be space-efficient 5/1/2020 9
Overview Of Bitmap Indexing Bitmap indexing is a query execution optimization technique in Data Warehousing environments Oracle provides OLTP & Data Warehousing in one engine 5/1/2020 10
Overview Of Bitmap Indexing Oracle Supports Ever-growing Types of Indexes: – B*-tree – B*-tree Cluster – Hash – Reverse Key – Function-Based – Bitmap Index – Bitmap Join Index (new in Oracle 9i) 5/1/2020 11
Characteristics Of Bitmap Indexes Bitmap index entries have Bitmap vectors of ‘0’s and ‘1’s: <0 1 0 1 00000 1 0000000010>... Each 1-bit in the Bitmap corresponds to a rowid inside a table: – Bitmap:
5/1/2020 12
Characteristics Of A Bitmap Index 1-bits correspond to rowids A mapping function converts the bit position to an actual rowid A compression function compresses the long sequence of ‘0’s in the Bitmap Good for low-mid-high cardinality columns 5/1/2020 13
Structure & Size of A Bitmap Index?
Index entries which contain bitmap/bit-vector , instead of list of rowids (B*-tree) Each bit in bitmap maps to a rowid inside a table Bitmap index entry structure: –
Structure & Size of A B*tree Index Uncompressed: –
B*-tree vs. Bitmap Index Storage
10 5 0 25 20 15 1 Million Rows
Cardinality 5/1/2020 16
Are all High Cardinality Columns Inappropriate for Bitmap Indexing?
Suppose we have CA as index entry (8 clustered occurrences: CA CA CA CA CA CA CA CA… TX Bitmapped: Size = (key + 2* 6 byte + bitmap) = key + 12 + ~8=
key + ~20
Uncompressed B*-tree: Size = (n * key + n * 6 byte) = 8 * key + 48 = 8 key + 48 Compressed B*-tree: Size = (key + n * 6 byte) =
key + 48
5/1/2020 17
Are all High Cardinality Columns Inappropriate for Bitmap Indexing?
Indexed a table with: – 31,029 rows, num_distinct = 3879 – Column values were clustered: …3 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4...
9 9 9 9 9 9 9 9...
15 15 15 15 15 15 15 15... 27 27 27 27 27 27 27 27… – Bitmap, B*tree, and compressed B*-tree indexes were built on this column – BM index Size: – B*-tree index Size: – B*-tree Index Compressed: 167,631 bytes 997,619 bytes 449,602 bytes 5/1/2020 18
Oracle Manual: “As a general rule, a [degree of] cardinality of under 1% makes a good candidate for a bitmap index” Index Type BM B*-tree B*-tree Comp ressed Key Occurences: 8 Table size:31,029 rows num_distinct = 3879 Degree of Cardinality= 12.5% --------------------------- Index Size 167,631 bytes 997,619 bytes 449,602 bytes Key Occurences: 3 Table size:31,029 rows num_distinct = 10,344 Degree of Cardinality= 33.3% ---------------------------- Index Size 438,827 bytes 997,343 bytes 625,853 bytes Key Occurences: 1 Table size:31,029 rows num_distinct = 31,029 Degree of Cardinality= 100% ------------------------ Index Size 1,277,145 bytes 996,792 bytes 1,183,722 Bytes!!?
5/1/2020 19
Characteristics Of A Bitmap Index Bitmap indexes are very space efficient, which allow more entries per leaf block.
A bitmap can hold many bits pointing to many rowids (low cardinality columns) Significantly fewer index block processing and disk I/O.
When a bitmap index entry is locked, many rows stay locked (in OLTP) In a B*-tree index, a list of rowids are stored for each index entry in a leaf block and, when an index entry is locked, it will not impact many table rows 5/1/2020 20
Characteristics Of A Bitmap Index Bitmaps are stored in the leaves of a B-tree index as bitmap segments Used on low/mid/high cardinality data Fast Boolean/Set operations on Bitmap values from different Index entries Example • Bitmap AND, OR, AND-NOT ...
5/1/2020 21
Characteristics Of A Bitmap Index Bitmap Indexes can be combined with other Bitmap indexes and B-tree indexes in the same access path Fully Updateable Parallelism Applied to: All Aspects of Queries & Index Creation 5/1/2020 22
Performance Considerations Of Bitmap Indexing Problem?
Concurrent DML (updates/inserts/deletes) operations can be problematic I.e., not suitable for concurrent OLTP workloads Granularity of lock on Bitmap index segment = 1 Each index segment can hold hundreds of bits One lock per Index segment 5/1/2020 23
Performance Considerations Of Bitmap Indexing Batched DMLs are done efficiently - Each bitmap index segment is modified by a single statement (not transaction) in one trip, once a segment lock is acquired: insert into table customer (select * from temp_table) 5/1/2020 24
Logical Layout of Bitmap Indexes Oracle implements B-tree structures to store Bitmaps for each indexed key Up to 30 keys can be specified for each composite index Bitmaps are broken down into chunks (not exceeding half a database block) They are laid out as:
...
...
end rowid 11.7
bitmap 1000101000011000 5/1/2020 25
Logical Layout of Bitmap Indexes
Bitmaps are stored in the leaves of B-tree indexes as bitmap segments
Aligned mapping in place between bitmaps and table rows BLOCK10
Key Red Blue yellow Green Start Rowid 10.0
10.0
10.0
10.0
End Rowid 12.7
12.7
12.7
12.7
BLOCK 11
Bitmap 1 000 1 00 1 000 1 00 1 000000 00 1 00000 11 000000 1 0000 0 1 0000 1 00000000000 1 00 000 1 000000 1 00 1 000 1 000
BLOCK12
5/1/2020 26
Logical Layout of Bitmap Indexes 1st Problem?
Insertion of new rows misalign the mapping in place between Bitmaps and table rows Explanation I?
Oracle divides up each block into maximum number of slots: – Based on minimum row size, – (Minimum row size would be derived from definition of each column in table) 5/1/2020 27
Logical Layout of Bitmap Indexes Oracle map bits to table rows based on maximum number of rows per data block – bits-allocated-per-table-block – And not to existing rows in the data block Excessive zeros are compressed 5/1/2020 28
Mapping Bitmaps to Rowids Optimization Explanation II?
Populate table 1st alter table XYZ minimize records_per_block (see “nominimize” to disable) Build bitmap indexes now Oracle scans table for maximum number of records in any block Oracle restricts the table to this maximum records/block Fewer bits go to each block Potentially smaller bitmap indexes -- run a test!
5/1/2020 29
Mapping Bitmaps to Rowids Optimization 2nd Problem?
This approach creates a dependency between table column definition & Bitmaps created Altering table definition, changing minimum row length, may result in all bitmap indexes being automatically invalidated, demanding index rebuilds 5/1/2020 30
Creation Of Bitmap indexes A Bitmap Index may be constructed on one or more columns of a table In Oracle 9, a local bitmap index can be created on a partitioned table 5/1/2020 31
Creation Of Bitmap indexes ( 1st Salve Set) Full table scan to fetch values of column(s) Column values are fed into bitmap generator to create index entries:
Creation Of Bitmap indexes
Second set of Parallel Salves
Index Build Bitmap Compaction Sort
Initial set of
Bitmap Construction Table Access 33
Creation Of Bitmap indexes 2nd Salve Set Bitmap index entries sorted on:
<
key
><
startrowid
>
… Set: sort_area_size Index entries compaction: to piece together bitmaps of the same key to reach half a database block size Placing index entries into a B-tree structure 5/1/2020 34
Bitmap Compression Patented algorithm (GennadyAntoshenkov), Cleverly encoded, hence very low overhead Storage Policy: – Store all 0-bits if next 1-bit is < 8 bits away: ( 1 000 1 00 1 000 1 00 1 000000 1 ) – Store 0-bits length if distance to next 1-bit is >= 8 bits: ( 1 0000000000000 1 00000000 1 00000000000000 1 ) 5/1/2020 35
Query Processing & Bitmap Access Methods Bitmap Index Probe (for: equality and/or range predicates) Bitmap AND (set-based) (Intersection among multiple bitmaps ) Bitmap OR (set-based) (Union among multiple bitmaps) 5/1/2020 36
Query Processing & Bitmap Access Methods Bitmap MINUS (set-based) (between two bitmaps) Bitmap COUNT (set-based) Bitmap Merge (bitmap OR on bitmap values) Bitmap Conversion ( bitmap conversion to: rowid or count bits) 5/1/2020 37
Equality Predicates select count(*) from customer where region=‘WEST’
Add all counts
together Aggregation
Count all 1-bits
in the bitmaps fetched Bitmap Conversion (To Count) Fetch the
bitmap
corresponding to region=‘west’ in the bitmap index Bitmap Index Probe (region=‘WEST’) 38 5/1/2020
AND Predicates select name from customer where state=‘GA’ and gender=‘F’ Table Access (By ROWID) Bitmap Conversion (To ROWIDS) Bitmap AND Bitmap Index state=‘GA’ Bitmap Index (gender=‘F’) 39 5/1/2020
OR Predicates select count(*) from customer where state=‘FL’ OR state is NULL Aggregation Bitmap Conversion (To COUNT) Bitmap OR 5/1/2020 Bitmap Index (state=‘FL’) Bitmap Index (state is NULL) 40
Not Equal Predicates select count(*) from customer where gender =‘F’ and state !=‘VA’ Assumption: State is declared NOT NULL Aggregation Bitmap Conversion (To COUNT) 5/1/2020 Bitmap Index (state=‘VA’) Bitmap MINUS Bitmap Index (gender = ‘F’) 41
Range Predicates select count(*) from customer Aggregation where gender=‘F’ AND age > 65 Bitmap Conversion (To COUNT) Bitmap AND BITMAP_MERGE_AREA_SIZE?
Bitmap Merge Bitmap Index (gender= ‘F’) Bitmap Index (age > 65) 5/1/2020 42
Group By Queries select state, count(*) from customer group by state No need to sort for grouping Aggregation (Group By Nosort) Bitmap Conversion (To Count) Count 1-bits in each bitmap return key Key & bitmap will be returned in order 5/1/2020 from bitmap Bitmap Index (Full Scan) 43
Distinct Queries select distinct state from customer Sort (Unique Nosort) Only the keys will be returned for each bitmap index entry 5/1/2020 Bitmap Index (Full Scan) 44
Combining Predicates Table Access (By ROWID) select name from customer where income > 90000 AND gender = ‘F’ Bitmap Conversion (To ROWID) Bitmap AND Reverse not done!
Bitmap Conversion (From ROWID) Bitmap Index (gender = ‘F’) rowids sorted Generates all rowids satisfying the predicate 5/1/2020 Sort B-tree Index (Income > 90000) 45
Star Transformation What makes bitmap indexes so powerful are their ability to combine with same/other types of indexes So far we have learned how to combine indexes to retrieve data from a table Now we will learn how to combine indexes to rapidly handle joins 5/1/2020 46
What a DBA Needs to Know About Oracle’s Star Transformation Processing In a Star Schema?
Part II
Vilin Roufchaie, Cingular Wireless [email protected]
Download Slides:www.nocoug.org
5/1/2020 47
Presentation Summary Part II Star Schema, Join, & Transformation Star Optimization, and Transformation CBO Estimation & Query Transformer Enabling Star Transformation Star Transformation Steps 5/1/2020 48
Presentation Summary Part II Joinback Elimination Case Study & Explain Plan Review To “Star Query”? Or To “Star Transform” ?
Conclusions Acknowledgements 5/1/2020 49
5/1/2020 Star Transformation (ST)
Star Transformation Is a Cost-Based Query Transformation Aimed at Executing Star Queries Efficiently In a Star Schema
50
Star Schema
Is the Basic design of a data warehouse
Made up of: –One or more fact tables –A few dimension tables
5/1/2020 51
Times Products 5/1/2020 Employee Sales Customers 52
Fact Table Contains all the “quantitative information” that the user wants to see in the result set Foreign keys to dimension tables Non-key columns contains numeric facts: summarized, analyzed, and reported Narrow in record width Usually huge number of rows Examples: Sales, Shipment 5/1/2020 53
Dimension Tables Contains the “qualitative information” defining how users will analyze fact data Primary Key column Non-key columns contain “descriptive information” about a record Therefore wide in record length Denormalized, enhances query performance Examples: Time, product, employee 5/1/2020 54
What is a Star Join?
5/1/2020 A join process in which dimension tables’
Primary Key
column values are joined to the fact table’s
Foreign Key
column values in
Star Schemas
(but the dimension tables are not joined to each other) 55
Star Transformation Oracle Data Warehousing functions equally apply to:
Star
schemas
3rd Normal Form
schemas
Hybrid
schemas 5/1/2020 56
Star Transformation ST is Powerful feature of Oracle utilizing bitmap indexing to handle Star Joins: – ST Handles: Several dimension tables, Snowflakes, Views More than a single fact table (example: sales & inventory) Complex Queries (Inside-out) – Predicate constraint on fact column(s) – (should build BM index on fact’s non-key column ) Parallelizable (by fact table rowid ranges) 5/1/2020 57
Star Transformation Data Warehousing
Capabilities
that work with all schema models are: Partitioning Parallelism Materialized Views 5/1/2020 58
Star Transformation (vs.Star Optimization) Good for large number of dimension tables The Fact tables are sparsely/densely populated Ideal for creating & combining single-column bitmap indexes on fact columns (rather than concatenated indexes) Appropriate for cases where large number of dimensions would lead to large Cartesian products finding few matching rows in the fact table 5/1/2020 59
(Star Transformation vs.) Star Optimization Good for small number of dimension tables (with relatively small number of rows) Fact table should be densely populated Does not work well with sparsely populated facts Relies on computing a Cartesian product of the dimension tables, based on the WHERE clause predicates In the last step , joins the result set to a fact table via NESTED LOOPS through concatenated B*-tree index access path.
5/1/2020 60
What Does CBO Do?
At the outset, the CBO DOES take indexing cost into account when evaluating a query. It creates two plans: – Regular – Transformed And picks the least costly plan to execute the star query 5/1/2020 61
5/1/2020 Query Transformer & Plan Generator (CBO) 62
Enabling and Implementing Star Transformation Set init.ora parameter: star_transformation_enabled = TRUE Create: Single-column bitmap indexes on a fact table’s dimension keys (foreign-keys) Create indexes on dimension tables’ attribute columns found in a query’s WHERE clause (also known as dimension filters ) Analyze tables and indexes in the schema for the CBO 5/1/2020 63
Star Transformation Passes Two passes are performed by the optimizer. 1st pass: Matching fact rows (result set) are retrieved via bitmap 2nd pass: Joins the “result set” to the dimension tables (usually hash join) This technique is called
semi-join
5/1/2020 64
Star Transformation Processing Star Transformation Example: Select c.Name, s.Price
from Employee e , Product p , Customer c , Sales s where e .Empid = s .Empid
and p .Productid = s .Productid
and c .Customerid= s .Customerid
and c.
Income > 10000 p.
Supplier = ‘Oracle’ e.
Department = ‘Telesales’ and and 5/1/2020 65
Star Transformation Processing 1st Phase: Identify and retrieve the needed rows from the fact table by implementing sub-select queries Select c.Name, s.Price
from Employee e , Product p , Customer c , Sales s where e .Empid = s .Empid
p .Productid = c .Customerid = s s .Productid
.Customerid and and and s.Productid in (select p.Productid from Product p where p.Supplier = ‘Oracle’ ) and s.Empid
in (select e.Empid from Employee e where e.Department = ‘Telesales’ ) and s.Customerid
in (select c.Customerid from Customer c wher c.Income > 100000 ) 5/1/2020 66
Star Transformation Processing Summary Of Steps -- 1st Phase A dimension table predicate is executed to return a subset of rows from the dimension – (SQL's creating dimensions’ temporary segments (ORA_TEMP_1_4, etc.) can be retrieved from the shared pool) – (A dimension must sufficiently constrain the fact table to qualify, otherwise access will be deferred to 2nd phase by the CBO) 5/1/2020 67
Star Transformation Processing Summary Of Steps -- 1st Phase For each value in the return set, a bitmap index is retrieved for the corresponding fact table column ( Bitmap Key Iteration step ) Bitmap merge operation is performed for all bitmap values through a bitmap OR operation Previous 3 steps are repeated for the remaining dimension tables with predicate constraints 5/1/2020 68
Star Transformation Processing Summary Of Steps -- 1st Phase Bitmap elimination of all bitmap merge values of all fact rows not joining all dimension tables are done through a bitmap AND operation ( Bitmap AND ) The final, merged bitmap set is then converted to
rowids
( Bitmap Conversion To Rowid) 5/1/2020 69
Star Transformation Processing Summary Of Steps -- 2nd Phase The final
fact
table rowid set is used to join the dimension tables, utilizing the most efficient joining algorithm available (usually a Hash Join ) 5/1/2020 70
Star Transformation Processing Joinback Elimination Select c.Name, s.Price
from Employee e, Product p, Customer c , Sales s where e.Empid = s.Empid
and p.Productid = s.Productid
c.Customerid=s.Customerid
c.Income > 10000 p.Supplier = ‘Oracle’ e.Department = ‘Telesales’ and and and and 5/1/2020 71
Star Transformation Processing Bitmap Conversion to rowid Bitmap AND Bitmap Merge Bitmap Key Iteration Employee Table (Department=‘TeleSales’) sales.Empid Bitmap Index (Empid=:variable) 72
Star Transformation Joinback Elimination Hash Join Employee Table (Department = ‘Telesales’) Hash Join Hash Join Product Table (Supplier = ‘Oracle’) 5/1/2020 Customer Table (Income > 100000) Sales Table (By ROWID) 73
5/1/2020 Case Study
TABLE_NAME ------------------------------
PRESCRIPTIONS (fact)
RESIDENTS PRODUCTS
DOCTORS FACILITIES TIME AHFS_GENERAL AHFS_SPECIFIC PHARMACIES DISPFUNC
NUM_ROWS ------------------------
11000326
805204 501945
51416 7856 3866 229 534 54 11 74
5/1/2020 Case Study Query 1 has two low-cardinality, range and equality predicates as shown below in blue font:
SELECT
AL3.NAME AS ColAL1 , AL2.DTE AS ColAL2 , AL7.REGION AS ColAL3 , AL7.CITY AS ColAL4 , AL6.NAME AS ColAL5 , AL2.MONTHNAME
AS ColAL6 , COUNT ( AL1.PRESCRIPTION_KEY ) AS ColAL7
FROM
PRESCRIPTIONS
TIME
AL1,
AL2, PRODUCTS
FACILITIES
AL3,
AL6,
PHARMACIES
WHERE AL7
(AL3.PRODUCT_KEY= AL1.PRODUCT_KEY AND AL3.PHARMACY_ID= AL1.PHARMACY_KEY AL2.TIME_KEY= AL1.TIME_KEY AND AL6.FACILITY_KEY= AL1.FACILITY_KEY AL7.PHARMACY_KEY= AL1.PHARMACY_KEY)
( AL3.GENERIC_CODE='10200' AL2.MONTHNAME IN ('Apr97', 'Feb97’))
GROUP BY
AND
AND AND AL3.NAME, AL2.DTE, AL7.REGION, AL7.CITY, AL6.NAME, AL2.MONTHNAME; AND 75
5/1/2020 Case Study
The key to high performance of star queries depends on the existences of
low-cardinality, foreign-key
columns in the fact table, suitable for creating bitmap indexes,
Along with presence of
one or multiple low-cardinality predicate constraints ,
which should, in turn, reduce the count of bitmap indexes in a fact table.
76
Case Study What low-cardinality predicate signifies is a low percentage of returned rows from a dimension table when the predicate is executed. This will result in significantly reducing the number of semi-joins of the dimension rows against the fact table. The example below illustrates the point:
Operations -----------------------------------------
select count(*) from TIME where monthname in ('Apr97','Feb97');
Cardinality (%) Number of Rows ----------------------------- --------------------
57/3,866= 1.48
TIME=3866 5/1/2020 77
Case Study Tuning Steps Analyzed all tables in the FROM clause (use DBMS_STATS ) Single-column bitmap indexes table’s dimension keys were built on fact Primary Key indexes were created on dimension tables’ join columns Appropriate indexes were built on dimension filters Explained the query in question Init.ora
parameters were set 78
Case study Explain Plan A) bitmap values of fact rows are retrieved from bitmap index,
BMAPTIMEKEY,
executing TIME table’s predicate constraint, TIME.MONTHNAME IN ('Apr97', 'Feb97’)
,
followed by a
BITMAP MERGE
of all such bitmap values B) bitmap values of fact rows are retrieved from bitmap index,
BMAPPRODUCTKEY,
executing PRODUCT table’s predicate constraint, PRODUCTS.GENERIC_CODE='10200' , followed by a
BITMAP MERGE
of all such bitmap values C)
BITMAP AND
operation does bitmap elimination of all bitmap merge values, followed by
BITMAP CONVERSION
to rowid of the final merged bitmap set D) Hash join operation of PRODUCT’s two columns and PRESCRIPTIONS’ (fact) rowid set begins here E) Hash join operation of dimension tables PHARMACIES, TIME , FACILITIES and remaining rowid set from fact happens one after the other heretofore 5/1/2020 79
Case study
OPERATION ========================================== ===============
SELECT STATEMENT SORT HASH JOIN
OPTIONS
GROUP BY HASH JOIN HASH JOIN HASH JOIN TABLE ACCESS BY INDEX ROWID INDEX TABLE ACCESS
BITMAP CONVERSION
BITMAP AND
BITMAP MERGE
BITMAP KEY ITERATION
OBJECT_NAME ==================
RANGE SCAN BY INDEX ROWID PRODUCTS GENERIC_CODE_IDX PRESCRIPTIONS
D TO ROWIDS C
TABLE ACCESS INDEX BITMAP INDEX
BITMAP MERGE
BITMAP KEY ITERATION BY INDEX ROWID RANGE SCAN RANGE SCAN PRODUCTS
B
GENERIC_CODE_IDX BMAPPRODUCTKEY TABLE ACCESS BITMAP INDEX TABLE ACCESS TABLE ACCESS TABLE ACCESS 5/1/2020 FULL RANGE SCAN FULL FULL FULL TIME
A
BMAPTIMEKEY PHARMACIES TIME FACILITIES
E
80
AND
Case Study
Init.ora Details: compatible = 9.x.x.x.x
star_transformation_enabled=TRUE bitmap_merge_area_size=8,388608 create_bitmap_area_size= 4,096,000 db_file_multiblock_read_count = 16 db_block_buffers = 22,000 shared_pool_size=8,192,000
5/1/2020 81
Star Query Or Star Transformation?
Query Pattern Star Query Star Trans formation Predefined.
To use single composite key Undefined.
Use bitmap index No/Size of Dim ension tables Small Large Fact Table Data Densely populated Sparsely populated FK Columns Cardinality High Dim ension Filters/ Pre dicates Better if Restricti ve If Space Will Be An Issue No If Using Snowflake Schema No If Using Com plex Queries No Low Use only if restricti ve enough Yes Yes Yes. Build BM index on non FK fact columns 5/1/2020 82
Conclusion Reject transformation plan, if other plans prove less costly The performance of Oracle8/9 Star Transformation algorithm is proportional to the amount of data retrieved from the fact table The performance gain associated with using Star Transformation must outweigh the processing cost incurred by applying star transformation to a SQL query 5/1/2020 83
Conclusion Do not force a: /+* STAR */. Other execution plans might work better Star Transformation with A Bitmap Join Index not covered.
5/1/2020 84
Acknowledgements
References & Technical Resources ------------------------------------------------
Hakan Jakobsson, Principal Member of Technical Staff, Oracle Corporation, Redwood Shores, CA., USA Star Queries in Oracle8. George Lumpkin, Product manager, Data Warehousing, Oracle Corporation, Redwood Shores, CA., USA Bitmap Indexing In Oracle 7.3 and 8.0, Cetin Ozbutun, Oracle Corporation, Redwood Shores, CA., USA Practical Oracle 8i, Building Efficient Databases, Jonathan Lewis, 2001 by Addison-Wesley Star query versus star transformation query: which to choose? Michael Janesch, Innovative Consulting, Inc. 2001 Oracle 9i Performance Tuning Tips and Techniques, Richard J Niemiec, Bradley Brown, Joseph Trezzo. 2003 Oracle Press Oracle 9i documentation 5/1/2020 85