Using ORA_HASH for Large Table Operations

View All Scripts Login to Run Script

Script Name Using ORA_HASH for Large Table Operations
Description Using ORA_HASH to assist with Large Table Tasks such as COUNT operation where a table size is over a few million records. This method is primarily used in Oracle Golden Gate Veridata to split up a Table into a few buckets for Data Comparisons.
Area Performance and Tuning
Contributor Stephen Cloete
Created Wednesday August 26, 2020

Statement 1

An example of creating a large dataset

Create a Test View over a large dataset

CREATE OR REPLACE VIEW my_sales_test AS   
SELECT * FROM   
(SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales  
UNION ALL  
SELECT * FROM sh.sales)  
ORDER BY cust_id DESC

View created.

Statement 2
View the output of the statement to ensure the records exceed 11 million records
Perform a Count on the View
```
SELECT COUNT(cust_id) FROM my_sales_test
```
```
COUNT(CUST_ID)
11944959
```
Statement 3
By running the statement , we can view the impact of the query on the database
Build the Explain Plan on the Statement
```
EXPLAIN PLAN FOR   
SELECT COUNT(cust_id) FROM my_sales_test
```
```
Statement processed.
```

COUNT(CUST_ID)
11944959

Statement 4

Take note of the of the impact on the database based on the Rows , Bytes, Cost %

Review the Explain Plan Result Set

SELECT * FROM TABLE(DBMS_XPLAN.display)


PLAN_TABLE_OUTPUT
Plan hash value: 319141534
 
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                |     1 |    13 |  5293   (1)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                  |                |     1 |    13 |            |          |       |       |
|   2 |   VIEW                           |                |    11M|   148M|  5293   (1)| 00:00:01 |       |       |
|   3 |    UNION-ALL                     |                |       |       |            |          |       |       |
|   4 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|   5 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|   6 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|   7 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|   8 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|   9 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  10 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  11 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  12 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  13 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  14 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  15 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  16 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  17 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  18 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  19 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  20 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  21 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  22 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  23 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  24 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  25 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  26 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  27 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  28 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  29 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  30 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  31 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  32 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  33 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  34 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  35 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  36 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  37 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  38 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  39 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  40 |     PARTITION RANGE ALL          |                |   918K|  4486K|   407   (0)| 00:00:01 |     1 |    28 |
|  41 |      BITMAP CONVERSION TO ROWIDS |                |   918K|  4486K|   407   (0)| 00:00:01 |       |       |
|  42 |       BITMAP INDEX FAST FULL SCAN| SALES_CUST_BIX |       |       |            |          |     1 |    28 |
-------------------------------------------------------------------------------------------------------------------

49 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 319141534

-------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \| Pstart\| Pstop \|
-------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 13 \| 5293 (1)\| 00:00:01 \| \| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 13 \| \| \| \| \|
\| 2 \| VIEW \| \| 11M\| 148M\| 5293 (1)\| 00:00:01 \| \| \|
\| 3 \| UNION-ALL \| \| \| \| \| \| \| \|
\| 4 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 5 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 6 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 7 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 8 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 9 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 10 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 11 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 12 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 13 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 14 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 15 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 16 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 17 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 18 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 19 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 20 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 21 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 22 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 23 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 24 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 25 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 26 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 27 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 28 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 29 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 30 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 31 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 32 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 33 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 34 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 35 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 36 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 37 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 38 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 39 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 40 \| PARTITION RANGE ALL \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| 1 \| 28 \|
\| 41 \| BITMAP CONVERSION TO ROWIDS \| \| 918K\| 4486K\| 407 (0)\| 00:00:01 \| \| \|
\| 42 \| BITMAP INDEX FAST FULL SCAN\| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
-------------------------------------------------------------------------------------------------------------------

Statement 5

In this example , the ORA_HASH function has been implemented

Perform the same query using the ORA_HASH operation

SELECT  
(SELECT COUNT(cust_id) AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 0)  
+             
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 1)  
+             
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 2)  
+            
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 3)  
+              
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 4)  
+              
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 5)  
AS "Total"   
    FROM DUAL


Total
11944959

Total
11944959

Statement 6

Once again , build up the Explain Plan

Build the Explain Plan on the Statement

EXPLAIN PLAN FOR  
SELECT  
(SELECT COUNT(cust_id) AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 0)  
+             
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 1)  
+             
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 2)  
+            
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 3)  
+              
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 4)  
+              
(SELECT COUNT(cust_id)  AS bucket  
FROM my_sales_test   
WHERE ORA_HASH(  
    cust_id, --expr  
        5    --max_bucket  
            ) = 5)  
AS "Total"   
FROM DUAL

Statement processed.

Statement 7

Review the database impact when using the ORA_HASH functionality , the impact on the database would have decreased as the ORA_HASH function first performs its bucketing operation before performing the Count operation.

Review the Explain Plan Result Set

SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY())


PLAN_TABLE_OUTPUT
Plan hash value: 1165283922
 
-----------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                                             |                |     1 |       | 31796   (1)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE                                              |                |     1 |    13 |            |          |       |       |
|   2 |   VIEW                                                       |                |   119K|  1516K|  5299   (1)| 00:00:01 |       |       |
|   3 |    UNION-ALL                                                 |                |       |       |            |          |       |       |
|   4 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|   5 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|*  6 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|   7 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|   8 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|*  9 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  10 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  11 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 12 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  13 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  14 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 15 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  16 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  17 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 18 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  19 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  20 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 21 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  22 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  23 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 24 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  25 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  26 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 27 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  28 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  29 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 30 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  31 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  32 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 33 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  34 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  35 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 36 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  37 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  38 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 39 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  40 |     PARTITION RANGE ALL                                      |                |  9188 | 45940 |   408   (1)| 00:00:01 |     1 |    28 |
|  41 |      BITMAP CONVERSION TO ROWIDS                             |                |  9188 | 45940 |   408   (1)| 00:00:01 |       |       |
|* 42 |       BITMAP INDEX FAST FULL SCAN                            | SALES_CUST_BIX |       |       |            |          |     1 |    28 |
|  43 |        SORT AGGREGATE                                        |                |     1 |    13 |            |          |       |       |
|  44 |         VIEW                                                 |                |   119K|  1516K|  5299   (1)| 00:00:01 |       |       |

Rows 1 - 50. More rows exist.

Statement 8
Drop the Test View once testing has been completed
Tidy Up
```
DROP VIEW my_sales_test
```
```
View dropped.
```

PLAN_TABLE_OUTPUT
Plan hash value: 1165283922

-----------------------------------------------------------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \| Pstart\| Pstop \|
-----------------------------------------------------------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| \| 31796 (1)\| 00:00:02 \| \| \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 13 \| \| \| \| \|
\| 2 \| VIEW \| \| 119K\| 1516K\| 5299 (1)\| 00:00:01 \| \| \|
\| 3 \| UNION-ALL \| \| \| \| \| \| \| \|
\| 4 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 5 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 6 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 7 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 8 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 9 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 10 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 11 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 12 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 13 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 14 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 15 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 16 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 17 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 18 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 19 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 20 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 21 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 22 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 23 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 24 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 25 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 26 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 27 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 28 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 29 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 30 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 31 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 32 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 33 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 34 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 35 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 36 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 37 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 38 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 39 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 40 \| PARTITION RANGE ALL \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| 1 \| 28 \|
\| 41 \| BITMAP CONVERSION TO ROWIDS \| \| 9188 \| 45940 \| 408 (1)\| 00:00:01 \| \| \|
\|* 42 \| BITMAP INDEX FAST FULL SCAN \| SALES_CUST_BIX \| \| \| \| \| 1 \| 28 \|
\| 43 \| SORT AGGREGATE \| \| 1 \| 13 \| \| \| \| \|
\| 44 \| VIEW \| \| 119K\| 1516K\| 5299 (1)\| 00:00:01 \| \| \|

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database