Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Data Warehouse Design Mistakes 3: Date Dimensions Without Date Primary Keys

This post is part of a series that discusses some common issues in data warehouses.

Good Practice 

It is not uncommon to see a time dimension with one row for every day. This approach saves putting functions on the date column of the fact table.  For example, in the Oracle Sales History sample schema:

  • The primary key on the time dimension is a date.
  • There are 37 different attribute columns. This saves coding complex SQL functions to group dates.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Name                                      Null?    Type
----------------------------------------- -------- -----------
TIME_ID NOT NULL DATE
DAY_NAME NOT NULL VARCHAR2(9)
DAY_NUMBER_IN_WEEK NOT NULL NUMBER(1)
DAY_NUMBER_IN_MONTH NOT NULL NUMBER(2)
CALENDAR_WEEK_NUMBER NOT NULL NUMBER(2)
FISCAL_WEEK_NUMBER NOT NULL NUMBER(2)
WEEK_ENDING_DAY NOT NULL DATE
WEEK_ENDING_DAY_ID NOT NULL NUMBER
CALENDAR_MONTH_NUMBER NOT NULL NUMBER(2)
FISCAL_MONTH_NUMBER NOT NULL NUMBER(2)
CALENDAR_MONTH_DESC NOT NULL VARCHAR2(8)
CALENDAR_MONTH_ID NOT NULL NUMBER
FISCAL_MONTH_DESC NOT NULL VARCHAR2(8)
FISCAL_MONTH_ID NOT NULL NUMBER
DAYS_IN_CAL_MONTH NOT NULL NUMBER
DAYS_IN_FIS_MONTH NOT NULL NUMBER
END_OF_CAL_MONTH NOT NULL DATE
END_OF_FIS_MONTH NOT NULL DATE
CALENDAR_MONTH_NAME NOT NULL VARCHAR2(9)
FISCAL_MONTH_NAME NOT NULL VARCHAR2(9)
CALENDAR_QUARTER_DESC NOT NULL CHAR(7)
CALENDAR_QUARTER_ID NOT NULL NUMBER
FISCAL_QUARTER_DESC NOT NULL CHAR(7)
FISCAL_QUARTER_ID NOT NULL NUMBER
DAYS_IN_CAL_QUARTER NOT NULL NUMBER
DAYS_IN_FIS_QUARTER NOT NULL NUMBER
END_OF_CAL_QUARTER NOT NULL DATE
END_OF_FIS_QUARTER NOT NULL DATE
CALENDAR_QUARTER_NUMBER NOT NULL NUMBER(1)
FISCAL_QUARTER_NUMBER NOT NULL NUMBER(1)
CALENDAR_YEAR NOT NULL NUMBER(4)
CALENDAR_YEAR_ID NOT NULL NUMBER
FISCAL_YEAR NOT NULL NUMBER(4)
FISCAL_YEAR_ID NOT NULL NUMBER
DAYS_IN_CAL_YEAR NOT NULL NUMBER
DAYS_IN_FIS_YEAR NOT NULL NUMBER
END_OF_CAL_YEAR NOT NULL DATE
END_OF_FIS_YEAR NOT NULL DATE

I have seen some time dimensions with 200 years of dates (eg 1900 – 2100), this is fine if your queries specify a date range on the dimension, but if you have an unbounded inequality such as simply after a date then you will get a lot of rows from the time dimension. This can also bring challenges, so keep it sensible.

Not Good Practice 

You should represent a date as a date and not as a string or a number.

  • 31st December 2018
  • Not 20181231 
  • Not '20181231' 

If you represent a date as a string you can get miscalculations in the optimizer.

  • For example, the difference between 31st December 2018 and 1st January 2019 should be 1 day.
  • However, if you use the string representation of a day it is 20190101-20181231=8870 
  • This can lead to bad cardinality estimates, and in more complex queries it can cause subsequent bad optimizer decisions later in the query. 

I will create a copy of the TIMES dimension, but with a numeric primary key, and I will add the original date in a new attribute column CALENDAR_DATE.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT /*+APPEND*/ INTO bad_times
( TIME_ID, CALENDAR_DATE, DAY_NAME
...
)
SELECT
TO_NUMBER(TO_CHAR(TIME_ID,'YYYYMMDD')) time_id, TIME_ID calendar_date, DAY_NAME
...
FROM times
/

TIME_ID is now a number that contains the date string, and CALENDAR_DATE is the original date value

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">   TIME_ID CALENDAR_
---------- ---------
19980101 01-JAN-98
19980102 02-JAN-98
19980103 03-JAN-98
19980104 04-JAN-98
19980105 05-JAN-98
19980106 06-JAN-98
19980107 07-JAN-98
19980108 08-JAN-98
19980109 09-JAN-98
19980110 10-JAN-98
...

I will also create a copy of sales where I have used the same numeric format for TIME_ID. The foreign key between these is still on TIME_ID, but that column is now a number.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT /*+APPEND*/ INTO bad_sales
(prod_id, cust_id, time_id, channel_id, promo_id, quantity_sold, amount_sold)
SELECT prod_id, cust_id, TO_NUMBER(TO_CHAR(time_id,'yyyymmdd')), channel_id, promo_id, quantity_sold, amount_sold
FROM sales
/

I will demonstrate the problem with a simple monthly sales analysis report for FY 1999.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT  t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
FROM sales s
, customers u
, products p
, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND t.time_id >= TO_DATE('27121998','DDMMYYYY')
AND t.time_id < TO_DATE('27121999','DDMMYYYY')
GROUP BY t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
ORDER BY 1
/

Plan hash value: 3667272686

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 165 (100)| | | | 13 |00:00:00.28 | 500 | 303 | | | |
| 1 | SORT GROUP BY | | 1 | 365 | 21170 | 165 (14)| 00:00:01 | | | 13 |00:00:00.28 | 500 | 303 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 365 | 21170 | 164 (13)| 00:00:01 | | | 365 |00:00:00.28 | 500 | 303 | 1298K| 1298K| 1527K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 366 | 8784 | 14 (0)| 00:00:01 | | | 365 |00:00:00.01 | 14 | 0 | | | |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| TIMES | 1 | 366 | 8784 | 14 (0)| 00:00:01 | | | 365 |00:00:00.01 | 14 | 0 | | | |
|* 5 | INDEX RANGE SCAN | TIMES_PK | 1 | 366 | | 3 (0)| 00:00:01 | | | 365 |00:00:00.01 | 3 | 0 | | | |
| 6 | VIEW | VW_GBC_5 | 1 | 366 | 12444 | 150 (14)| 00:00:01 | | | 365 |00:00:00.28 | 486 | 303 | | | |
| 7 | HASH GROUP BY | | 1 | 366 | 4758 | 150 (14)| 00:00:01 | | | 365 |00:00:00.28 | 486 | 303 | 1063K| 1063K| 2552K (0)|
| 8 | PARTITION RANGE AND | | 1 | 230K| 2924K| 133 (4)| 00:00:01 |KEY(AP)|KEY(AP)| 247K|00:00:00.21 | 486 | 303 | | | |
|* 9 | TABLE ACCESS FULL | SALES | 5 | 230K| 2924K| 133 (4)| 00:00:01 |KEY(AP)|KEY(AP)| 247K|00:00:00.21 | 486 | 303 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("ITEM_1"="T"."TIME_ID")
5 - access("T"."TIME_ID">=TO_DATE(' 1998-12-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "T"."TIME_ID"=TO_DATE(' 1998-12-27 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

The literal predicate on T.TIME_ID is repeated on S.TIME_ID in the predicate section of the execution plan due to the optimizer performing transitive closure.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT  t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
from bad_sales s
, customers u
, products p
, bad_times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND t.calendar_date >= TO_DATE('27121998','DDMMYYYY')
AND t.calendar_date < TO_DATE('27121999','DDMMYYYY')
GROUP BY t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
ORDER BY 1
/

Plan hash value: 4232725394

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 509 (100)| | | | 13 |00:00:00.15 | 540 | | | |
| 1 | SORT GROUP BY | | 1 | 366 | 22692 | 509 (19)| 00:00:01 | | | 13 |00:00:00.15 | 540 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 366 | 22692 | 508 (18)| 00:00:01 | | | 365 |00:00:00.15 | 540 | 1335K| 1335K| 1207K (0)|
| 3 | JOIN FILTER CREATE | :BF0001 | 1 | 366 | 10980 | 16 (0)| 00:00:01 | | | 365 |00:00:00.01 | 56 | | | |
| 4 | PART JOIN FILTER CREATE | :BF0000 | 1 | 366 | 10980 | 16 (0)| 00:00:01 | | | 365 |00:00:00.01 | 56 | | | |
|* 5 | TABLE ACCESS FULL | BAD_TIMES | 1 | 366 | 10980 | 16 (0)| 00:00:01 | | | 365 |00:00:00.01 | 56 | | | |
| 6 | VIEW | VW_GBC_5 | 1 | 1460 | 46720 | 492 (19)| 00:00:01 | | | 370 |00:00:00.15 | 483 | | | |
| 7 | HASH GROUP BY | | 1 | 1460 | 16060 | 492 (19)| 00:00:01 | | | 370 |00:00:00.15 | 483 | 1079K| 1079K| 2561K (0)|
| 8 | JOIN FILTER USE | :BF0001 | 1 | 918K| 9870K| 423 (6)| 00:00:01 | | | 250K|00:00:00.10 | 483 | | | |
| 9 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 9870K| 423 (6)| 00:00:01 |:BF0000|:BF0000| 250K|00:00:00.09 | 483 | | | |
|* 10 | TABLE ACCESS FULL | BAD_SALES | 5 | 918K| 9870K| 423 (6)| 00:00:01 |:BF0000|:BF0000| 250K|00:00:00.09 | 483 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

If we do the same query on the new CALENDAR_DATE attribute column on BAD_TIMES dimension we still expect 366 rows from TIMES, but then we expect 918K rows from BAD_SALES instead of 230K rows from SALES.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT  t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
FROM bad_sales s
, customers u
, products p
, bad_times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND t.time_id >= 19982712
AND t.time_id < 19992712
GROUP BY t.fiscal_year, t.fiscal_month_id, t.fiscal_month_desc
ORDER BY 1
/

Plan hash value: 1098183223

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 176 (100)| | | | 13 |00:00:00.16 | 459 | | | |
| 1 | SORT GROUP BY | | 1 | 445 | 24030 | 176 (18)| 00:00:01 | | | 13 |00:00:00.16 | 459 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 445 | 24030 | 175 (18)| 00:00:01 | | | 365 |00:00:00.16 | 459 | 1355K| 1355K| 1540K (0)|
| 3 | PART JOIN FILTER CREATE| :BF0000 | 1 | 445 | 9790 | 16 (0)| 00:00:01 | | | 365 |00:00:00.01 | 56 | | | |
|* 4 | TABLE ACCESS FULL | BAD_TIMES | 1 | 445 | 9790 | 16 (0)| 00:00:01 | | | 365 |00:00:00.01 | 56 | | | |
| 5 | VIEW | VW_GBC_5 | 1 | 470 | 15040 | 159 (19)| 00:00:01 | | | 365 |00:00:00.16 | 402 | | | |
| 6 | HASH GROUP BY | | 1 | 470 | 5170 | 159 (19)| 00:00:01 | | | 365 |00:00:00.16 | 402 | 1079K| 1079K| 2576K (0)|
| 7 | PARTITION RANGE AND | | 1 | 295K| 3177K| 138 (7)| 00:00:01 |KEY(AP)|KEY(AP)| 247K|00:00:00.10 | 402 | | | |
|* 8 | TABLE ACCESS FULL | BAD_SALES | 4 | 295K| 3177K| 138 (7)| 00:00:01 |KEY(AP)|KEY(AP)| 247K|00:00:00.09 | 402 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

But, now if I use the numeric value of the date in the predicate it incorrectly calculates the cardinality of TIMES as 445 instead of 366. The estimate of rows from BAD_SALES is not as far off as the previous query at only 296K rows, but it is still higher than the original 230K.
We see that if you query the BAD_TIMES dimension by an attribute column, then things will mostly work quite well, but if you put a criterion directly on the TIME_ID column, you will get cardinality misestimates. The queries in this example are very simple, but if more tables were joined, cardinality misestimates would have more opportunity to cause plan regressions.

Workaround

If you are stuck with a numeric primary key on your time dimension that you cannot change, you might be able to change the value you actually put into it. If the TIME_ID was the Julian date value all the arithmetic would correctly because there would be no gaps between months and years.

#8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184;">
#4472C4; border-right: none; border: solid #4472C4 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-left-alt: solid #4472C4 .5pt; mso-border-left-themecolor: accent1; mso-border-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
#4472C4; border-bottom: solid #4472C4 1.0pt; border-left: none; border-right: none; border-top: solid #4472C4 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
From
#4472C4; border-bottom: solid #4472C4 1.0pt; border-left: none; border-right: none; border-top: solid #4472C4 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
To
#4472C4; border-bottom: solid #4472C4 1.0pt; border-left: none; border-right: none; border-top: solid #4472C4 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Difference
#4472C4; border-left: none; border: solid #4472C4 1.0pt; mso-background-themecolor: accent1; mso-border-bottom-alt: solid #4472C4 .5pt; mso-border-bottom-themecolor: accent1; mso-border-right-alt: solid #4472C4 .5pt; mso-border-right-themecolor: accent1; mso-border-themecolor: accent1; mso-border-top-alt: solid #4472C4 .5pt; mso-border-top-themecolor: accent1; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Function
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Date
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
31st December 2018
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1st January 2019
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
#8EAADB 1.0pt; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Number
#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
20181231
#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
20190101
#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
8070
#8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TO_CHAR(…,'YYYMMDD')
#D9E2F3; border-top: none; border: solid #8EAADB 1.0pt; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-themecolor: accent1; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Julian
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2458484
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2458485
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1
#D9E2F3; border-bottom: solid #8EAADB 1.0pt; border-left: none; border-right: solid #8EAADB 1.0pt; border-top: none; mso-background-themecolor: accent1; mso-background-themetint: 51; mso-border-alt: solid #8EAADB .5pt; mso-border-bottom-themecolor: accent1; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #8EAADB .5pt; mso-border-left-themecolor: accent1; mso-border-left-themetint: 153; mso-border-right-themecolor: accent1; mso-border-right-themetint: 153; mso-border-themecolor: accent1; mso-border-themetint: 153; mso-border-top-alt: solid #8EAADB .5pt; mso-border-top-themecolor: accent1; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TO_CHAR(….'J')

Data Warehouse Design Mistakes 1: Lack of Foreign Key Constraints

This post is part of a series that discusses some common issues in data warehouses.

What is the Purpose of Foreign Keys?

  • In the context of relational databases, a foreign key is a column, or group of columns, on one table that uniquely identifies a row in another table.  So, a foreign key on a child record uniquely identifies a row on the parent table.
  • If a foreign key constraint is enforced (which they are when the foreign key constraint is enabled, which they are by default) then you cannot insert a child for which you cannot find a parent, and you cannot delete a parent for which children exist.  This guarantees that data is, and remains, referentially integral.
A foreign key constraint can also permit the Oracle optimizer to perform certain optimisations in executing a SQL query.  
  • If you are querying a child and parent table (or if you prefer a fact and dimension table) without referencing any attribute on the parent (or dimension), then the optimizer can rely on the foreign key constraint to know that the parent row will always be present and so omit the parent table from the query.  This is called 'foreign key join elimination', and I will discuss this in more detail below.
I think that documenting relationships between tables through foreign keys helps developers build sensible SQL code that follows the data model, performs better, and is easier to understand and therefore easier to maintain.
If you have foreign key constraints defined in an OLTP system, it is typical to see them enabled exactly because they enforce referential integrity.
In a data warehouse there is less emphasis on revalidating data, but saving effort though join elimination is important.  Unfortunately, it is all too common to see downstream data warehouses without foreign key constraints at all.  It seems to be very easy to find an excuse not to build them. While researching this blog I came across this blog posting that I thought had the ring of truth of about it.

"9 Reasons Why There Are No Foreign Keys in Your Database" (Piotr Kononow)

  1. Performance: degrades DML performance as foreign keys are validated
  2. Legacy data is not referentially integral in the first place.
  3. Full Table Reload.  Should disable, reload, and then re-enable and revalidate constraints.
  4. High-Level Framework doesn't create foreign keys.
  5. Cross-Database relations
  6. Database platform agnosticism (eg. PeopleSoft)
  7. Open for Change
  8. Lazy Architect
  9. Table relationships are not clear/revealed.

I'll add one more unacceptable excuse this list.

  • Extracting referentially integral data from an OLTP system into the data warehouse, so there is no need for more foreign keys to revalidate it there again.

Foreign Key Join Elimination

If you are querying a child and parent table, without referencing any attribute on the parent, then the optimizer can rely on the foreign key constraint to know that the parent row will always be present and so omit the parent table from the query.  
Let's start with a very simple demonstration, on the Sales History demo schema, of a query of the fact table (SALES), and three dimension tables (PRODUCTS, TIMES, CUSTOMERS).  I am referencing attribute columns on the PRODUCT and TIMES tables, but nothing on the CUSTOMERS table other than the primary key column CUST_ID in a join predicate.

Without Foreign Key Constraints

I have disabled the foreign key constraints between these tables to model the situation without foreign key constraints.
#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE sales MODIFY CONSTRAINT sales_channel_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_product_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_promo_fk DISABLE NOVALIDATE;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk DISABLE NOVALIDATE;

This is a query for certain products (Electronics) in a single year (1999).

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT  p.prod_category
, t.fiscal_year
, COUNT(*)
FROM sales s
, products p
, times t
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND t.fiscal_year = 2001
AND c.cust_id = s.cust_id
AND p.prod_category = 'Electronics'
AND p.prod_category = 'Software/Other'
GROUP BY p.prod_category
, t.fiscal_year
ORDER BY 1
/
Throughout these tests, having set STATISTICS_LEVEL=ALL in order to collect additional runtime statistics, I have will extract execution plans, as follows

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">select * from table(dbms_xplan.display_cursor(null,null,'ADVANCED +ADAPTIVE PROJECTION +ALLSTATS LAST, IOSTATS'));

You can see that all 4 tables (or indexes on them) referenced in the SQL appear in the execution plan (in bold).

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1167 (100)| | | | 1 |00:00:00.21 | 3386 | 472 | | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 44 | 1167 (3)| 00:00:01 | | | 1 |00:00:00.21 | 3386 | 472 | | | |
| 2 | NESTED LOOPS | | 1 | 2840 | 122K| 1167 (3)| 00:00:01 | | | 2840 |00:00:00.21 | 3386 | 472 | | | |
| 3 | VIEW | VW_GBF_35 | 1 | 2840 | 108K| 1167 (3)| 00:00:01 | | | 2840 |00:00:00.19 | 544 | 472 | | | |
| 4 | HASH GROUP BY | | 1 | 2840 | 138K| 1167 (3)| 00:00:01 | | | 2840 |00:00:00.19 | 544 | 472 | 1137K| 1137K| 1403K (0)|
|* 5 | HASH JOIN | | 1 | 41362 | 2019K| 1163 (3)| 00:00:01 | | | 23678 |00:00:00.17 | 544 | 472 | 1695K| 1695K| 1571K (0)|
| 6 | PART JOIN FILTER CREATE | :BF0000 | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 7 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 8 | HASH JOIN | | 1 | 165K| 6156K| 1146 (3)| 00:00:01 | | | 26637 |00:00:00.17 | 488 | 472 | 1572K| 1572K| 1390K (0)|
|* 9 | VIEW | index$_join$_002 | 1 | 13 | 273 | 2 (0)| 00:00:01 | | | 13 |00:00:00.01 | 5 | 0 | | | |
|* 10 | HASH JOIN | | 1 | | | | | | | 13 |00:00:00.01 | 5 | 0 | 1355K| 1355K| 1376K (0)|
|* 11 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 1 | 13 | 273 | 1 (0)| 00:00:01 | | | 13 |00:00:00.01 | 1 | 0 | | | |
| 12 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1 | 13 | 273 | 1 (0)| 00:00:01 | | | 72 |00:00:00.01 | 4 | 0 | | | |
| 13 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 14M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.13 | 482 | 472 | | | |
| 14 | TABLE ACCESS FULL | SALES | 5 | 918K| 14M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.13 | 482 | 472 | | | |
|* 15 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 2840 | 1 | 5 | 0 (0)| | | | 2840 |00:00:00.01 | 2842 | 0 | | | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

With Foreign Key Constraints

I have re-enabled the foreign key constraints

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE sales MODIFY CONSTRAINT sales_channel_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_product_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_promo_fk enable validate;
ALTER TABLE sales MODIFY CONSTRAINT sales_time_fk enable validate;

Now the CUSTOMERS table is no longer referenced due to foreign key join elimination.  The nested loop operation (at line 2 of the previous plan) to join the CUSTOMERS table is no longer required because the query does not need anything from that dimension table.  The foreign key constraint tells the optimizer that there will always be a corresponding row for every SALES record.  Thus not joining to it does not change the result of the query.  There is one less operation requiring PGA memory, and the number of buffers required, and the optimizer cost has also dropped.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1163 (100)| | | | 1 |00:00:00.87 | 544 | 472 | | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 45 | 1163 (3)| 00:00:01 | | | 1 |00:00:00.87 | 544 | 472 | | | |
|* 2 | HASH JOIN | | 1 | 41362 | 1817K| 1163 (3)| 00:00:01 | | | 23678 |00:00:00.85 | 544 | 472 | 1695K| 1695K| 1669K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 4 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 165K| 5346K| 1146 (3)| 00:00:01 | | | 26637 |00:00:00.79 | 488 | 472 | 1572K| 1572K| 1331K (0)|
|* 6 | VIEW | index$_join$_002 | 1 | 13 | 273 | 2 (0)| 00:00:01 | | | 13 |00:00:00.01 | 5 | 0 | | | |
|* 7 | HASH JOIN | | 1 | | | | | | | 13 |00:00:00.01 | 5 | 0 | 1355K| 1355K| 1377K (0)|
|* 8 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 1 | 13 | 273 | 1 (0)| 00:00:01 | | | 13 |00:00:00.01 | 1 | 0 | | | |
| 9 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1 | 13 | 273 | 1 (0)| 00:00:01 | | | 72 |00:00:00.01 | 4 | 0 | | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 10M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.43 | 482 | 472 | | | |
| 11 | TABLE ACCESS FULL | SALES | 5 | 918K| 10M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.25 | 482 | 472 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- rely constraint used for this statement

From Oracle 12.2, when a constraint has been used to remove a table from an execution plan, you get a note in the execution plan "rely constraint used for this statement".  Though it doesn't tell you which table has been eliminated, nor due to which constraint.
Admittedly, the actual run time of the query has gone up because there is more work for the optimizer to do.  However, this is an unusually small test, so it has swamped the savings in not visiting the CUSTOMERS table.  Foreign key join elimination should generally deliver a performance improvement because the query processes less data.

With Disabled Reliable Constraints

If you don't want the overhead and complexity of enforced foreign key constraints, you can still get the benefit of foreign key join elimination.  It is possible to disable the constraint from SALES to CUSTOMER so that it is not enforced, but it can still tell the optimizer that it can RELY upon the referential integrity of the data as if the constraint were enforced.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE sales MODIFY CONSTRAINT sales_customer_fk RELY DISABLE NOVALIDATE;

This behaviour has been available since constraints were introduced in Oracle 8 (c. 1998), but there has been a change in Oracle 12c.  Foreign key join elimination on disabled RELY constraints does not occur if QUERY_REWRITE_INTEGRITY is set to its default value of ENFORCED.  It must be set to either TRUSTED or STALE_TOLERATED.  However, bear in mind that setting it to STALE_TOLERATED also affects how materialized views can be used by query rewrite.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER SESSION SET query_rewrite_integrity = TRUSTED;

And now we are back to the same plan as before without the CUSTOMERS table, and with the "rely constraint used for this statement" note.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1164 (100)| | | | 1 |00:00:01.55 | 544 | 472 | | | |
| 1 | SORT GROUP BY NOSORT | | 1 | 1 | 45 | 1164 (3)| 00:00:01 | | | 1 |00:00:01.55 | 544 | 472 | | | |
|* 2 | HASH JOIN | | 1 | 81133 | 3565K| 1164 (3)| 00:00:01 | | | 110K|00:00:01.46 | 544 | 472 | 1476K| 1476K| 1528K (0)|
|* 3 | VIEW | index$_join$_002 | 1 | 26 | 546 | 2 (0)| 00:00:01 | | | 26 |00:00:00.01 | 5 | 0 | | | |
|* 4 | HASH JOIN | | 1 | | | | | | | 26 |00:00:00.01 | 5 | 0 | 1298K| 1298K| 1612K (0)|
|* 5 | INDEX RANGE SCAN | PRODUCTS_PROD_CAT_IX | 1 | 26 | 546 | 1 (0)| 00:00:01 | | | 26 |00:00:00.01 | 1 | 0 | | | |
| 6 | INDEX FAST FULL SCAN | PRODUCTS_PK | 1 | 26 | 546 | 1 (0)| 00:00:01 | | | 72 |00:00:00.01 | 4 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 229K| 5369K| 1160 (3)| 00:00:01 | | | 246K|00:00:01.00 | 538 | 472 | 1695K| 1695K| 1683K (0)|
| 8 | PART JOIN FILTER CREATE | :BF0000 | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
|* 9 | TABLE ACCESS FULL | TIMES | 1 | 364 | 4368 | 16 (0)| 00:00:01 | | | 364 |00:00:00.01 | 55 | 0 | | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 10M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.47 | 482 | 472 | | | |
| 11 | TABLE ACCESS FULL | SALES | 5 | 918K| 10M| 1136 (2)| 00:00:01 |:BF0000|:BF0000| 296K|00:00:00.28 | 482 | 472 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- rely constraint used for this statement

Multi-Column Foreign Key Join Elimination 

It has always been possible to create foreign keys on multiple columns, sometimes called composite foreign keys.  From Oracle 12.2, it is possible to get join elimination on composite foreign keys. Jonathan Lewis has published a blog with demonstration scripts.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE child 
add constraint child_fk_parent foreign key (id_g, id_p)
references parent (id_g, id);

However, he has also found a case where join elimination depends on the order of tables in the from clause – (currently unpublished) bug 22228669.  The workaround is to list parents in the from clause before their children.
The need for single column keys inevitably leads to meaningless keys, often generated from a sequence.  Another of Jonathan's blogs and its comments discusses the pros and cons. 
Updated 19th November: It depends how important foreign key join elimination is to you.  How much use a system makes of this feature will depend upon how it is written.  If you think that you may need to use this feature then, at least until this bug is resolved, I would stick with single column keys in Oracle 12.2.

Summary of Good Practice

  • Primary keys (with unique indexes) on all tables.
  • Define foreign key constraints on all dimension columns of fact tables referencing the primary or unique keys on the dimension tables through equality joins only. 
    • I think that even from Oracle 12.2 I would still avoid creating multi-column primary and foreign keys because there appears to be a bug with this feature.
  • If foreign key constraints are to be enforced, then the key columns should also be indexed avoid TM locking during DML operations.
  • Otherwise, in order to achieve join elimination mark the constraints as reliable:
  • #eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE … MODIFY CONSTRAINT … RELY NOVALIDATE DISABLE
    • From Oracle 12 you must also set QUERY_REWRITE_INTEGRITY to TRUSTED or STALE_TOLERATED.

Presenting at AUSOUG Connect 2018 Conference in Melbourne, 21 November 2018.

  After initially not being in a position to make it this year, I will now be presenting at the AUSOUG Connect 2018 Conference in Melbourne this coming Wednesday, 21 November 2018. My presentation will be: “12c Release 2 and 18c – New Indexing Related Features” Oracle Database 12.2 and 18.3 releases have introduced a number […]

Data Warehouse Design Mistakes 2: Effective Dating Dimensions

This post is part of a series that discusses some common issues in data warehouses.

I have seen situations where some dimensions are effective-dated.  That is to say that there are multiple rows in the dimension table for the same main dimension id, but for different date ranges.  At least one of the date columns has to become part of the primary key.
The dimensions in the Sales History sample schema have got effective from and to dates, but these columns are not part of the primary key.  Let's imagine that the products get updated every year and a new version of the product is sold, requiring a new dimension row.

The Wrong Way

I am going to build a copy of the PRODUCTS table with a new effective dated row for each calendar year in which the product was sold.  The structure and indexing of the BAD_PROUCTS dimension table is unchanged, except that the primary key is not just on PROD_ID but now additionally includes PROD_EFF_FROM

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE TABLE bad_products (
prod_id NUMBER(6) NOT NULL,
prod_name VARCHAR2(50) NOT NULL,
prod_desc VARCHAR2(4000) NOT NULL,
prod_subcategory VARCHAR2(50) NOT NULL,
prod_subcategory_id NUMBER NOT NULL,
prod_subcategory_desc VARCHAR2(2000) NOT NULL,
prod_category VARCHAR2(50) NOT NULL,
prod_category_id NUMBER NOT NULL,
prod_category_desc VARCHAR2(2000) NOT NULL,
prod_weight_class NUMBER(3) NOT NULL,
prod_unit_of_measure VARCHAR2(20) ,
prod_pack_size VARCHAR2(30) NOT NULL,
supplier_id NUMBER(6) NOT NULL,
prod_status VARCHAR2(20) NOT NULL,
prod_list_price NUMBER(8,2) NOT NULL,
prod_min_price NUMBER(8,2) NOT NULL,
prod_total VARCHAR2(13) NOT NULL,
prod_total_id NUMBER NOT NULL,
prod_src_id NUMBER ,
prod_eff_from DATE ,
prod_eff_to DATE ,
prod_valid VARCHAR2(1) )
/
ALTER TABLE bad_products
ADD CONSTRAINT bad_products_pk PRIMARY KEY (prod_id, prod_eff_from)
/

Now, I will populate BAD_PRODUCTS by copying PRODUCTS.  I will create rows for each product for each year in which it was sold.  I started with 72 rows in PRODUCTS, but I have 275 rows in BAD_PRODUCTS.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT INTO bad_products
WITH s AS (
SELECT s.prod_id
, TRUNC(s.time_id,'YYYY') time_id
from sales s
GROUP BY s.prod_id, TRUNC(s.time_id,'YYYY')
)
SELECT p.prod_id
,p.prod_name
,p.prod_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_subcategory
,p.prod_subcategory_id
,p.prod_subcategory_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_category
,p.prod_category_id
,p.prod_category_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_weight_class
,p.prod_unit_of_measure
,p.prod_pack_size
,p.supplier_id
,p.prod_status
,p.prod_list_price
,p.prod_min_price
,p.prod_total
,p.prod_total_id
,p.prod_src_id
,s.time_id PROD_EFF_FROM
,ADD_MONTHS(s.time_id,12)-1 PROD_EFF_TO
,p.prod_valid
from products p
, s
where s.prod_id = p.prod_id
/

Finally, I will build the same indexes as on the PRODUCTS table.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">CREATE INDEX bad_products_prod_cat_ix ON bad_products (prod_category);
CREATE BITMAP INDEX bad_products_prod_status_bix ON bad_products (prod_status);
CREATE INDEX bad_products_prod_subcat_ix ON bad_products (prod_subcategory);

I can't now create a foreign key for the product on the SALES table.
I can’t use PROD_ID only because that does not match the primary key.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE sales
ADD CONSTRAINT sales_bad_product_fk
FOREIGN KEY (prod_id) REFERENCES bad_products (prod_id)
;
ERROR at line 3:
ORA-02270: no matching unique or primary key for this column-list

I can't build the foreign key on PROD_ID and TIME_ID, because the TIME_IDs are merely inside the effective range but are not on the PRODUCTS table

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">ALTER TABLE sales
ADD CONSTRAINT sales_bad_product_fk
FOREIGN KEY (prod_id, time_id) REFERENCES bad_products (prod_id, prod_eff_from)
;
ERROR at line 2:
ORA-02298: cannot validate (SH.SALES_BAD_PRODUCT_FK) - parent keys not found

When I come to query the data, I have no choice but to code two inequality conditions on the product table with one of them on a column that is not part of the foreign key.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">
from sales s
, bad_products p
, times t
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND t.time_id >= p.prod_eff_from
AND (t.time_id <= p.prod_eff_to OR p.prod_eff_to IS NULL)

So, even on Oracle 12.2, even if I create the above multi-column constraint NOVALIDATE RELY and set QUERY_REWRITE_INTEGRITY to TRUSTED, I have no chance of being able to eliminate the product table with foreign key join elimination.

The Right Way 

If the product changes and we need a new dimension row for an updated product we will have a new product, with a new product ID, but will with effective dates for that year. Here, I have created an exact copy of the SALES table, called SALES2, but now the product ID is prefixed with the calendar year.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT /*+APPEND*/ INTO sales2
SELECT
prod_id+1000*TO_NUMBER(TO_CHAR(time_id,'YYYY')) prod_id
,cust_id
,time_id
,channel_id
,promo_id
,quantity_sold
,amount_sold
FROM sales
/

And similarly, I have created a copy of the PRODUCTS dimension table, called PRODUCTS2 with additional product rows for each year that the product was sold, but again the product ID has been prefixed with the calendar year. The primary key is still just PROD_ID and all the indexes and foreign keys are exactly the same as on the original tables.  Both BAD_PRODUCTS and PRODUCTS2 have the 275 rows.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">INSERT INTO products2
WITH s AS (
SELECT s.prod_id
, TRUNC(s.time_id,'YYYY') time_id
FROM sales s
GROUP BY s.prod_id, TRUNC(s.time_id,'YYYY')
)
SELECT p.PROD_ID+(1000*TO_NUMBER(TO_CHAR(s.time_id,'YYYY')))
,p.prod_name
,p.prod_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_subcategory
,p.prod_subcategory_id
,p.prod_subcategory_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_category
,p.prod_category_id
,p.prod_category_desc||' '||TO_CHAR(s.time_id,'YYYY')
,p.prod_weight_class
,p.prod_unit_of_measure
,p.prod_pack_size
,p.supplier_id
,p.prod_status
,p.prod_list_price
,p.prod_min_price
,p.prod_total
,p.prod_total_id
,p.prod_src_id
,s.time_id PROD_EFF_FROM
,ADD_MONTHS(s.time_id,12)-1 PROD_EFF_TO
,p.prod_valid
FROM products p
, s
WHERE s.prod_id = p.prod_id
/

This is effectively the same query on SALES2 and PRODUCTS2, and it returns the same results. I have kept the inequality criteria on the production effective dates with both SALES and TIMES, although they are not needed here. However, we will see that the way the tables are structured and accessed will have a cost implication.
This time I want to analyse software sales (product category 205) for the USA by fiscal year.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">SELECT c.country_name
, p.prod_category_id
, p.prod_category
, t.fiscal_year
, COUNT(*) num_sales
, SUM(s.amount_sold) total_amount_sold
FROM sales2 s
, customers u
, products2 p
, times t
, countries c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND u.cust_id = s.cust_id
AND u.country_id = c.country_id
AND c.country_iso_code = 'US'
AND p.prod_category_id = 205
AND t.time_id >= p.prod_eff_from
AND (t.time_id <= p.prod_eff_to OR p.prod_eff_to IS NULL)
AND s.time_id >= p.prod_eff_from
AND (s.time_id <= p.prod_eff_to OR p.prod_eff_to IS NULL)
GROUP BY c.country_name
, p.prod_category_id
, p.prod_category
, t.fiscal_year
ORDER BY 1,2
/

With Query Transformation

First the wrong way with the BAD_PROUCTS table

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2522704620

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 2000 (100)| | | | 5 |00:00:06.60 | 305K| | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 5 |00:00:06.60 | 305K| | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D7230_A4BC21 | 1 | | | | | | | 0 |00:00:00.08 | 1525 | 1024 | 1024 | |
|* 3 | HASH JOIN | | 1 | 2921 | 81788 | 418 (1)| 00:00:01 | | | 18520 |00:00:00.05 | 1524 | 1185K| 1185K| 721K (0)|
|* 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | | | |
| 6 | SORT GROUP BY | | 1 | 5 | 495 | 1582 (1)| 00:00:01 | | | 5 |00:00:06.52 | 303K| 2048 | 2048 | 2048 (0)|
|* 7 | HASH JOIN | | 1 | 11568 | 1118K| 1580 (1)| 00:00:01 | | | 237K|00:00:06.29 | 303K| 2290K| 1666K| 2158K (0)|
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7230_A4BC21 | 1 | 2921 | 43815 | 4 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | | | |
|* 9 | HASH JOIN | | 1 | 11568 | 948K| 1576 (1)| 00:00:01 | | | 237K|00:00:06.18 | 303K| 1744K| 1744K| 1704K (0)|
| 10 | TABLE ACCESS FULL | TIMES | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | | | |
|* 11 | HASH JOIN | | 1 | 30764 | 2163K| 1559 (1)| 00:00:01 | | | 237K|00:00:06.09 | 303K| 1115K| 1115K| 1352K (0)|
|* 12 | TABLE ACCESS FULL | BAD_PRODUCTS | 1 | 102 | 4284 | 5 (0)| 00:00:01 | | | 102 |00:00:00.01 | 14 | | | |
| 13 | VIEW | VW_ST_F7251F01 | 1 | 48359 | 1416K| 1554 (1)| 00:00:01 | | | 237K|00:00:05.02 | 303K| | | |
| 14 | NESTED LOOPS | | 1 | 48359 | 2361K| 1526 (1)| 00:00:01 | | | 237K|00:00:04.82 | 303K| | | |
| 15 | PARTITION RANGE ALL | | 1 | 48359 | 991K| 397 (2)| 00:00:01 | 1 | 28 | 237K|00:00:03.36 | 302K| | | |
| 16 | BITMAP CONVERSION TO ROWIDS | | 28 | 48359 | 991K| 397 (2)| 00:00:01 | | | 237K|00:00:03.15 | 302K| | | |
| 17 | BITMAP AND | | 28 | | | | | | | 16 |00:00:03.01 | 302K| | | |
| 18 | BITMAP MERGE | | 28 | | | | | | | 16 |00:00:00.04 | 1819 | 1024K| 512K|36864 (0)|
| 19 | BITMAP KEY ITERATION | | 28 | | | | | | | 1572 |00:00:00.03 | 1819 | | | |
| 20 | BUFFER SORT | | 28 | | | | | | | 2856 |00:00:00.01 | 9 | 73728 | 73728 | |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_PRODUCTS | 1 | 102 | 816 | 24 (0)| 00:00:01 | | | 102 |00:00:00.01 | 9 | | | |
| 22 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | | | 275 |00:00:00.01 | 1 | | | |
| 23 | BITMAP INDEX FULL SCAN | BAD_PRODUCTS_PROD_STATUS_BIX | 1 | | | | | | | 1 |00:00:00.01 | 1 | | | |
|* 24 | BITMAP INDEX RANGE SCAN | SALES_PROD_BIX | 2856 | | | | | 1 | 28 | 1572 |00:00:00.02 | 1810 | | | |
| 25 | BITMAP MERGE | | 28 | | | | | | | 16 |00:00:02.97 | 301K| 8316K| 1039K| 317K (0)|
| 26 | BITMAP KEY ITERATION | | 28 | | | | | | | 19186 |00:00:02.85 | 301K| | | |
| 27 | BUFFER SORT | | 28 | | | | | | | 296K|00:00:00.42 | 0 | 26M| 1871K| 865K (0)|
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7230_A4BC21 | 5 | 2921 | 14605 | 4 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | | | |
|* 29 | BITMAP INDEX RANGE SCAN | SALES_CUST_BIX | 296K| | | | | 1 | 28 | 19186 |00:00:01.97 | 301K| | | |
| 30 | TABLE ACCESS BY USER ROWID | SALES | 237K| 1 | 29 | 1157 (1)| 00:00:01 | ROWID | ROWID | 237K|00:00:00.92 | 829 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

11 - access("ITEM_3"="P"."PROD_ID")
filter(("ITEM_1">="P"."PROD_EFF_FROM" AND ("ITEM_1"<="P"."PROD_EFF_TO" OR "P"."PROD_EFF_TO" IS NULL)))

And now the right way with PRODUCTS2. At first glance the plans are not very different, both plans do a star transformation, but the devil is in the detail.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1207 (100)| | | | 5 |00:00:05.08 | 304K| | | |
| 1 | TEMP TABLE TRANSFORMATION | | 1 | | | | | | | 5 |00:00:05.08 | 304K| | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D7231_A4BC21 | 1 | | | | | | | 0 |00:00:00.07 | 1525 | 1024 | 1024 | |
|* 3 | HASH JOIN | | 1 | 2921 | 81788 | 418 (1)| 00:00:01 | | | 18520 |00:00:00.05 | 1524 | 1185K| 1185K| 689K (0)|
|* 4 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | |
| 5 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | | | |
| 6 | SORT GROUP BY | | 1 | 5 | 435 | 789 (1)| 00:00:01 | | | 5 |00:00:05.01 | 302K| 2048 | 2048 | 2048 (0)|
|* 7 | HASH JOIN | | 1 | 17849 | 1516K| 787 (1)| 00:00:01 | | | 237K|00:00:04.83 | 302K| 2290K| 1666K| 2123K (0)|
| 8 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7231_A4BC21 | 1 | 2921 | 43815 | 4 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | | | |
|* 9 | HASH JOIN | | 1 | 17849 | 1255K| 782 (1)| 00:00:01 | | | 237K|00:00:04.74 | 302K| 1744K| 1744K| 1704K (0)|
| 10 | TABLE ACCESS FULL | TIMES | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | | | |
|* 11 | HASH JOIN | | 1 | 17849 | 1045K| 766 (1)| 00:00:01 | | | 237K|00:00:04.68 | 302K| 1298K| 1298K| 1658K (0)|
|* 12 | TABLE ACCESS FULL | PRODUCTS2 | 1 | 102 | 2856 | 5 (0)| 00:00:01 | | | 102 |00:00:00.01 | 14 | | | |
| 13 | VIEW | VW_ST_F7251F01 | 1 | 17937 | 560K| 761 (1)| 00:00:01 | | | 237K|00:00:04.18 | 302K| | | |
| 14 | NESTED LOOPS | | 1 | 17937 | 963K| 733 (1)| 00:00:01 | | | 237K|00:00:03.99 | 302K| | | |
| 15 | PARTITION RANGE ALL | | 1 | 17936 | 402K| 395 (1)| 00:00:01 | 1 | 28 | 237K|00:00:02.76 | 302K| | | |
| 16 | BITMAP CONVERSION TO ROWIDS | | 28 | 17936 | 402K| 395 (1)| 00:00:01 | | | 237K|00:00:02.60 | 302K| | | |
| 17 | BITMAP AND | | 28 | | | | | | | 16 |00:00:02.49 | 302K| | | |
| 18 | BITMAP MERGE | | 28 | | | | | | | 16 |00:00:00.03 | 1700 | 1024K| 512K| 9216 (0)|
| 19 | BITMAP KEY ITERATION | | 28 | | | | | | | 399 |00:00:00.02 | 1700 | | | |
| 20 | BUFFER SORT | | 28 | | | | | | | 2856 |00:00:00.01 | 9 | 73728 | 73728 | |
|* 21 | TABLE ACCESS BY INDEX ROWID BATCHED| PRODUCTS2 | 1 | 102 | 1020 | 24 (0)| 00:00:01 | | | 102 |00:00:00.01 | 9 | | | |
| 22 | BITMAP CONVERSION TO ROWIDS | | 1 | | | | | | | 275 |00:00:00.01 | 1 | | | |
| 23 | BITMAP INDEX FULL SCAN | PRODUCTS2_PROD_STATUS_BIX | 1 | | | | | | | 1 |00:00:00.01 | 1 | | | |
|* 24 | BITMAP INDEX RANGE SCAN | SALES2_PROD_BIX | 2856 | | | | | 1 | 28 | 399 |00:00:00.01 | 1691 | | | |
| 25 | BITMAP MERGE | | 28 | | | | | | | 16 |00:00:02.46 | 300K| 8400K| 1050K| 312K (0)|
| 26 | BITMAP KEY ITERATION | | 28 | | | | | | | 19192 |00:00:02.36 | 300K| | | |
| 27 | BUFFER SORT | | 28 | | | | | | | 296K|00:00:00.33 | 0 | 26M| 1871K| 865K (0)|
| 28 | TABLE ACCESS FULL | SYS_TEMP_0FD9D7231_A4BC21 | 5 | 2921 | 14605 | 4 (0)| 00:00:01 | | | 18520 |00:00:00.01 | 0 | | | |
|* 29 | BITMAP INDEX RANGE SCAN | SALES2_CUST_BIX | 296K| | | | | 1 | 28 | 19192 |00:00:01.63 | 300K| | | |
| 30 | TABLE ACCESS BY USER ROWID | SALES2 | 237K| 1 | 32 | 366 (1)| 00:00:01 | ROWID | ROWID | 237K|00:00:00.76 | 830 | | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

9 - access("ITEM_3"="T"."TIME_ID")
11 - access("ITEM_2"="P"."PROD_ID")

The key differences are:

  • At line 24 of both plans. 1572 rows are returned from the SALES_PROD_BIX index because it is returning every effective dated software product, but only 399 rows are returned from SALES2_PROD_BID because the PROD_ID is different for every fiscal year.
  • At line 11, hashing the BAD_PRODUCTS table took 1.07s because it was necessary to evaluate the inequality conditions, whereas it only took 0.50s to hash PRODUCTS2 with a simple equality join. 
  • The optimizer cost with PRODUCTS2 is much lower, and the overall response time is 5.08s instead of 6.60s.

Without Query Transformation 

If I repeat the test without star transformation enabled, I again see differences in the hash joining of the product dimensions. First on BAD_PRODUCTS

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 2324880021

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1592 (100)| | | | 5 |00:00:01.94 | 5805 | 4128 | | | |
| 1 | SORT GROUP BY | | 1 | 11 | 1144 | 1592 (3)| 00:00:01 | | | 5 |00:00:01.94 | 5805 | 4128 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 9845 | 999K| 1590 (3)| 00:00:01 | | | 237K|00:00:01.74 | 5805 | 4128 | 1744K| 1744K| 1620K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | 0 | | | |
| 4 | TABLE ACCESS FULL | TIMES | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | 0 | | | |
|* 5 | HASH JOIN | | 1 | 26182 | 2352K| 1574 (3)| 00:00:01 | | | 237K|00:00:01.65 | 5700 | 4128 | 1115K| 1115K| 1390K (0)|
|* 6 | TABLE ACCESS FULL | BAD_PRODUCTS | 1 | 102 | 4284 | 5 (0)| 00:00:01 | | | 102 |00:00:00.01 | 14 | 0 | | | |
|* 7 | HASH JOIN | | 1 | 48360 | 2361K| 1568 (3)| 00:00:01 | | | 526K|00:00:01.07 | 5685 | 4128 | 2290K| 1666K| 2216K (0)|
|* 8 | HASH JOIN | | 1 | 2921 | 81788 | 418 (1)| 00:00:01 | | | 18520 |00:00:00.04 | 1524 | 0 | 1236K| 1236K| 771K (0)|
|* 9 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | 0 | | | |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.02 | 1521 | 0 | | | |
| 11 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 19M| 1142 (3)| 00:00:01 |:BF0000|:BF0000| 918K|00:00:00.77 | 4160 | 4128 | | | |
| 12 | TABLE ACCESS FULL | SALES | 20 | 918K| 19M| 1142 (3)| 00:00:01 |:BF0000|:BF0000| 918K|00:00:00.76 | 4160 | 4128 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("S"."TIME_ID"="T"."TIME_ID")
filter((("T"."TIME_ID"<="P"."PROD_EFF_TO" OR "P"."PROD_EFF_TO" IS NULL) AND "T"."TIME_ID">="P"."PROD_EFF_FROM"))
5 - access("S"."PROD_ID"="P"."PROD_ID")
filter(("S"."TIME_ID">="P"."PROD_EFF_FROM" AND ("S"."TIME_ID"<="P"."PROD_EFF_TO" OR "P"."PROD_EFF_TO" IS NULL)))

And then on PRODUCTS2

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 80%; line-height: 90%; overflow: auto; padding-left: 4px; padding-right: 4px; width: 95%;">Plan hash value: 1297664275

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | Pstart| Pstop | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 875 (100)| | | | 5 |00:00:00.92 | 3126 | | | |
| 1 | SORT GROUP BY | | 1 | 11 | 1012 | 875 (4)| 00:00:01 | | | 5 |00:00:00.92 | 3126 | 2048 | 2048 | 2048 (0)|
|* 2 | HASH JOIN | | 1 | 17849 | 1603K| 873 (4)| 00:00:01 | | | 237K|00:00:00.75 | 3126 | 1744K| 1744K| 1621K (0)|
| 3 | PART JOIN FILTER CREATE | :BF0000 | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | | | |
| 4 | TABLE ACCESS FULL | TIMES | 1 | 1826 | 21912 | 16 (0)| 00:00:01 | | | 1826 |00:00:00.01 | 55 | | | |
|* 5 | HASH JOIN | | 1 | 17849 | 1394K| 857 (4)| 00:00:01 | | | 237K|00:00:00.70 | 3070 | 1298K| 1298K| 1703K (0)|
|* 6 | TABLE ACCESS FULL | PRODUCTS2 | 1 | 102 | 2856 | 5 (0)| 00:00:01 | | | 102 |00:00:00.01 | 14 | | | |
|* 7 | HASH JOIN | | 1 | 48360 | 2455K| 852 (4)| 00:00:01 | | | 526K|00:00:00.59 | 3055 | 2290K| 1666K| 2216K (0)|
|* 8 | HASH JOIN | | 1 | 2921 | 81788 | 418 (1)| 00:00:01 | | | 18520 |00:00:00.04 | 1524 | 1236K| 1236K| 774K (0)|
|* 9 | TABLE ACCESS FULL | COUNTRIES | 1 | 1 | 18 | 2 (0)| 00:00:01 | | | 1 |00:00:00.01 | 2 | | | |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 55500 | 541K| 416 (1)| 00:00:01 | | | 55500 |00:00:00.03 | 1521 | | | |
| 11 | PARTITION RANGE JOIN-FILTER| | 1 | 918K| 21M| 426 (6)| 00:00:01 |:BF0000|:BF0000| 918K|00:00:00.35 | 1530 | | | |
| 12 | TABLE ACCESS FULL | SALES2 | 20 | 918K| 21M| 426 (6)| 00:00:01 |:BF0000|:BF0000| 918K|00:00:00.35 | 1530 | | | |

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  • One difference is where we hash join the product dimension. BAD_PRODUCTS takes 0.58s, PRODUCTS2 takes just 0.11s.
  • The Bloom filter on SALES2 takes just 0.35s, while the filter on SALES takes 0.76s and requires fewer buffers because the PROD_ID equality join is more selective.

Summary 

  • Effective dating leads to inequality conditions between fact and dimension tables.
    • Can't express inequalities in foreign key – it has to be an equality join to primary key/unique key.
    • It is impossible to achieve foreign key join elimination on these dimensions.
    • You have to do more work evaluating the inequality conditions when you eventually hash join these dimensions into the result set. 
    • Bloom Filters only work with equality predicates. They rough match data, but there can be false positive results. The subsequent hash join does an exact match on a reduced volume of data. The Bloom cannot reduce the data on the inequality conditions, so they return more data to the hash operation that then takes longer to execute.
  • Conclusion
    • You should rigorously follow the star schema principles so you can always define a foreign key between facts and dimensions.

num_index_keys

The title is the name of an Oracle hint that came into existence in Oracle 10.2.0.3 and made an appearance recently in a question on the rarely used “My Oracle Support” Community forum (you’ll need a MOS account to be able to read the original). I wouldn’t have found it but the author also emailed me the link asking if I could take a look at it.  (If you want to ask me for help – without paying me, that is – then posting a public question in the Oracle (ODC) General Database or SQL forums and emailing me a private link is the strategy most likely to get an answer, by the way.)

The question was about a very simple query using a straightforward index – with a quirky change of plan after upgrading from 10.2.0.3 to 12.2.0.1. Setting the optimizer_features_enable to ‘10.2.0.3’ in the 12.2.0.1 system re-introduced the 10g execution plan. Here’s the query:


SELECT t1.*
   FROM   DW1.t1
  WHERE   t1.C1 = '0001' 
    AND   t1.C2 IN ('P', 'F', 'C')
    AND   t1.C3 IN (
                    '18110034450001',
                    '18110034450101',
                    '18110034450201',
                    '18110034450301',
                    '18110034450401',
                    '18110034450501'
          );
 

Information supplied: t1 holds about 500 million rows at roughly 20 rows per block, the primary key index is (c1, c2, c3, c4), there are just a few values for each of c1, c2 and c4, while c3 is “nearly unique” (which, for clarity, was expanded to “the number of distinct values of c3 is virtually the same as the number of rows in the table”).

At the moment we don’t have any information about histograms and we don’t known whether or not “nearly unique” might still allow a few values of c3 to have a large number of duplicates, so that’s something we might want to follow up on later.

Here are the execution plans – the fast one (from 10g) first, then the slow (12c) plan – and you should look carefully at the predicate section of the two plans:


10g (pulled from memory with rowsource execution statistics enabled)
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |      1 |        |      6 |00:00:00.01 |      58 |      5 |
|   1 |  INLIST ITERATOR             |                  |      1 |        |      6 |00:00:00.01 |      58 |      5 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1               |     18 |      5 |      6 |00:00:00.01 |      58 |      5 |
|*  3 |    INDEX RANGE SCAN          | PK_T1            |     18 |      5 |      6 |00:00:00.01 |      52 |      4 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR
              "T1"."C2"='P')) AND (("C3"='18110034450001' OR "C3"='18110034450101' OR
              "C3"='18110034450201' OR "C3"='18110034450301' OR "C3"='18110034450401' OR
              "C3"='18110034450501')))

 

12c (from explain plan)
---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |     1 |   359 |     7   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR                     |                  |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1               |     1 |   359 |     7   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | PK_T1            |     1 |       |     6   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."C1"='0001' AND ("T1"."C2"='C' OR "T1"."C2"='F' OR
              "T1"."C2"='P'))
       filter("C3"='18110034450001' OR "C3"='18110034450101' OR
              "C3"='18110034450201' OR "C3"='18110034450301' OR
              "C3"='18110034450401' OR "C3"='18110034450501')
  

When comparing plans it’s better, of course, to present the same sources from the two systems, it’s not entirely helpful to have the generated plan from explain plan in one version and a run-time plan with stats in the other – given the choice I’d like to see the run-time from both. Despite this, I felt fairly confident that the prediction would match the run-time for 12c and that I could at least guess the “starts” figure for 12c.

The important thing to notice is the way that the access predicate in 10g has split into an access predicate followed by a filter predicate in 12c. So 12c is going to iterate three times (once for each of the values  ‘C’, ‘F’, ‘P’) and then walk a potentially huge linked list of index leaf blocks looking for 6 values of c3, while 10g is going to probe the index 18 times (3 combinations of c2 x six combinations of c3) to find “nearly unique” rows which means probably one leaf block per probe.

The 12c plan was taking minutes to run, the 10g plan was taking less than a second. The difference in execution time was probably the effect of the 12c plan ranging through (literally) thousands of index leaf blocks.

There are many bugs and anomalies relating to in-list iteration and index range scans and cardinality calculations – here’s a quick sample of v$system_fix_control in 12.2.0.1:


select optimizer_feature_enable ofe, sql_feature, bugno, description
from v$system_fix_control
where
	optimizer_feature_enable between '10.2.0.4' and '12.2.0.1'
and	(   sql_feature like '%CBO%'
	 or sql_feature like '%CARDINALITY%'
	)
and	(    lower(description) like '%list%'
	 or  lower(description) like '%iterat%'
	 or  lower(description) like '%multi%col%'
	)
order by optimizer_feature_enable, sql_feature, bugno
;

OFE        SQL_FEATURE                      BUGNO DESCRIPTION
---------- --------------------------- ---------- ----------------------------------------------------------------
10.2.0.4   QKSFM_CBO_5259048              5259048 undo unused inlist
           QKSFM_CBO_5634346              5634346 Relax equality operator restrictions for multicolumn inlists

10.2.0.5   QKSFM_CBO_7148689              7148689 Allow fix of bug 2218788 for in-list predicates

11.1.0.6   QKSFM_CBO_5139520              5139520 kkoDMcos: For PWJ on list dimension, use part/subpart bits

11.2.0.1   QKSFM_CBO_6818410              6818410 eliminate redundant inlist predicates

11.2.0.2   QKSFM_CBO_9069046              9069046 amend histogram column tracking for multicolumn stats

11.2.0.3   QKSFM_CARDINALITY_11876260    11876260 use index filter inlists with extended statistics
           QKSFM_CBO_10134677            10134677 No selectivity for transitive inlist predicate from equijoin
           QKSFM_CBO_11834739            11834739 adjust NDV for list partition key column after pruning
           QKSFM_CBO_11853331            11853331 amend index cost compare with inlists as filters
           QKSFM_CBO_12591120            12591120 check inlist out-of-range values with extended statistics

11.2.0.4   QKSFM_CARDINALITY_12828479    12828479 use dynamic sampling cardinality for multi-column join key check
           QKSFM_CARDINALITY_12864791    12864791 adjust for NULLs once for multiple inequalities on nullable colu
           QKSFM_CARDINALITY_13362020    13362020 fix selectivity for skip scan filter with multi column stats
           QKSFM_CARDINALITY_14723910    14723910 limit multi column group selectivity due to NDV of inlist column
           QKSFM_CARDINALITY_6873091      6873091 trim histograms based on in-list predicates
           QKSFM_CBO_13850256            13850256 correct estimates for transitive inlist predicate with equijoin

12.2.0.1   QKSFM_CARDINALITY_19847091    19847091 selectivity caching for inlists
           QKSFM_CARDINALITY_22533539    22533539 multi-column join sanity checks for table functions
           QKSFM_CARDINALITY_23019286    23019286 Fix cdn estimation with multi column stats on fixed data types
           QKSFM_CARDINALITY_23102649    23102649 correction to inlist element counting with constant expressions
           QKSFM_CBO_17973658            17973658 allow partition pruning due to multi-inlist iterator
           QKSFM_CBO_21057343            21057343 order predicate list
           QKSFM_CBO_22272439            22272439 correction to inlist element counting with bind variables

There are also a number of system parameters relating to inlists that are new (or have changed values) in 12.2.0.1 when compared with 10.2.0.3 – but I’m not going to go into those right now.

I was sufficiently curious about this anomaly that I emailed the OP to say I would be happy to take a look at the 10053 trace files for the query – the files probably weren’t going to be very large given that it was only a single table query – but in the end it turned out that I solved the problem before he’d had time to email them. (Warning – don’t email me a 10053 file on spec; if I want one I’ll ask for it.)

Based on the description I created an initial model of the problem – it took about 10 minutes to code:


rem     Tested on 12.2.0.1, 18.3.0.1

drop table t1 purge;

create table t1 (
	c1 varchar2(4) not null,
	c2 varchar2(1) not null,
	c3 varchar2(15) not null,
	c4 varchar2(4)  not null,
	v1 varchar2(250)
)
;

insert into t1
with g as (
	select rownum id 
	from dual
	connect by level <= 1e4 -- > hint to avoid wordpress format issue
)
select
	'0001',
	chr(65 + mod(rownum,11)),
	'18110034'||lpad(1+100*rownum,7,'0'),
	lpad(mod(rownum,9),4,'0'),
	rpad('x',250,'x')
from
	g,g
where
        rownum <= 1e5 -- > hint to avoid wordpress format issue
;


create unique index t1_i1 on t1(c1, c2, c3, c4);

begin
        dbms_stats.gather_table_stats(
                null,
                't1',
                method_opt => 'for all columns size 1'
        );
end;
/

alter session set statistics_level = all;
set serveroutput off

prompt	==========================
prompt	Default optimizer features
prompt	==========================

select
        /*+ optimizer_features_enable('12.2.0.1') */
	t1.*
FROM	t1
WHERE
	t1.c1 = '0001' 
AND	t1.c2 in ('H', 'F', 'C')
AND	t1.c3 in (
		'18110034450001',
		'18110034450101',
		'18110034450201',
		'18110034450301',
		'18110034450401',
		'18110034450501'
	)
;

select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

select 
        /*+ optimizer_features_enable('10.2.0.3') */
	t1.*
FROM	t1
WHERE
	t1.c1 = '0001' 
AND	t1.c2 in ('H', 'F', 'C')
AND	t1.c3 in (
		'18110034450001',
		'18110034450101',
		'18110034450201',
		'18110034450301',
		'18110034450401',
		'18110034450501'
	)
;

select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last'));

alter session set statistics_level = all;
set serveroutput off

The two queries produced the same plan – regardless of the setting for optimizer_features_enable – it was the plan originally used by the OP’s 10g setting:


-------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |      1 |        |    20 (100)|      0 |00:00:00.01 |      35 |
|   1 |  INLIST ITERATOR             |       |      1 |        |            |      0 |00:00:00.01 |      35 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |     18 |      2 |    20   (0)|      0 |00:00:00.01 |      35 |
|*  3 |    INDEX RANGE SCAN          | T1_I1 |     18 |      2 |    19   (0)|      0 |00:00:00.01 |      35 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR "T1"."C2"='H')) AND
              (("T1"."C3"='18110034450001' OR "T1"."C3"='18110034450101' OR "T1"."C3"='18110034450201' OR
              "T1"."C3"='18110034450301' OR "T1"."C3"='18110034450401' OR "T1"."C3"='18110034450501')))

There was one important difference between the 10g and the 12c plans – in 10g the cost of the table access (hence the cost of the total query) was 20; in 12c it jumped to 28 – maybe there’s a change in the arithmetic for costing the iterator, and maybe that’s sufficient to cause a problem.

Before going further it’s worth checking what the costs would look like (and, indeed, if the plan is possible in both versions) if we force Oracle into the “bad” plan. That’s where we finally get to the hint in the title of this piece. If I add the hint /*+ num_index_keys(t1 t1_i1 2) */ what’s going to happen ? (Technically I’ve included a hint to use the index, and specified the query block name to make sure Oracle doesn’t decide to switch to a tablescan):


select
        /*+
            optimizer_features_enable('12.2.0.1')
            index_rs_asc(@sel$1 t1@sel$1 (t1.c1 t1.c2 t1.c3 t1.c4))
            num_index_keys(@sel$1 t1@sel$1 t1_i1 2)
        */
        t1.*
FROM        t1
WHERE
        t1.c1 = '0001'
AND        t1.c2 in ('H', 'F', 'C')
AND        t1.c3 in (
                '18110034450001',
                '18110034450101',
                '18110034450201',
                '18110034450301',
                '18110034450401',
                '18110034450501'
        )
;

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |        |   150 (100)|      0 |00:00:00.01 |     154 |      1 |
|   1 |  INLIST ITERATOR                     |       |      1 |        |            |      0 |00:00:00.01 |     154 |      1 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T1    |      3 |     18 |   150   (2)|      0 |00:00:00.01 |     154 |      1 |
|*  3 |    INDEX RANGE SCAN                  | T1_I1 |      3 |     18 |   142   (3)|      0 |00:00:00.01 |     154 |      1 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("T1"."C1"='0001' AND (("T1"."C2"='C' OR "T1"."C2"='F' OR "T1"."C2"='H')))
       filter(("T1"."C3"='18110034450001' OR "T1"."C3"='18110034450101' OR "T1"."C3"='18110034450201' OR
              "T1"."C3"='18110034450301' OR "T1"."C3"='18110034450401' OR "T1"."C3"='18110034450501'))

This was the plan from 12.2.0.1 – and again the plan for 10.2.0.3 was identical except for costs which became 140 for the index range scan and 141 for the table access. At first sight it looks like 10g may be using the total selectivity of the entire query as the scaling factor for the index clustering_factor to find the table cost while 12c uses the cost of accessing the table for one iteration (rounding up) before multiplying by the number of iterations.

Having observed this detail I thought I’d do a quick test of what happened by default if I requested 145 distinct values of c3. Both versions defaulted to the access/filter path rather than the pure access path – but again there was a difference in costs. The 10g index cost was 140 with a table access cost of 158, while 12c had an index cost of 179 and a table cost of 372. So both versions switch plans at some point – do they switch at the same point ? Reader, I could not resist temptation, so I ran a test loop. With my data set the 12c version switched paths at 61 values in the in-list and 10g switched at 53 values –

Conclusion: there’s been a change in the selectivity calculations for the use of in-list iterators, which leads to a change in costs, which can lead to a change in plans; the OP was just unlucky with his data set and stats. Possibly there’s something about his data or stats that makes the switch appear with a much smaller in-list than mine.

Footnote:

When I responded to the thread on MOSC with the suggestion that the problem was in part due to statistics and might be affected by out of date stats (or a histogram on the (low-frequency) c2 column) the OP noted that stats hadn’t been gathered since some time in August – and found that the 12c path changed to the efficient (10g) one after re-gathering stats on the table.

 

Enable Index To Search For NULLs By Adding Constant to Index List. But Some Constants Better Than Others (Never Let Me Down)

By default, Oracle doesn’t index an entry if all columns within the index are NULL. However, (as I’ve blogged previously), it’s possible to index all possible NULL values by simply adding a constant value to the index column list. Importantly, the CBO knows when a column has all it’s NULL values indexed and can potentially […]

opatch investigations

This blogpost is about opatch and how to obtain information about the current oracle home(s), and how to obtain information about the patches to be applied.

Patches that can be applied using opatch are provided by oracle as zip files which have the following naming convention:
p[patchnumber]_[baseversion]_[platform]-[architecture].zip. The patch normally contains an XML file called ‘PatchSearch.xml’ and a directory with the patch number. Inside the patch number directory there is a README.txt which is lame, because it says ‘Refer to README.html’, and a README.html that contains the readme information that is also visible when the [README] button for this patch is selected in MOS.

I spend my time on the CLI exclusively. This is because I spend my time on remote servers all the time, and using the X window system would be unusable. The best part of using the CLI is that when done correctly, it gives you almost infinite control over what you do, while when clicking through an interface toggling selections and filling out fields makes you entering information that you have to copy from a document or make up on the spot, which then is quickly hidden by entering another tab or window. In fact, based on my experience, this is a guaranteed way of generating wrong or inconsistent results.

Because of being on the CLI almost exclusively on servers, I sometimes need to read the README.html. I can do that in MOS, but sometimes you want that information on the spot. You can open up the HTML file in ‘vim’ or ‘less’, but it will show you a lot of HTML making it very hard to read. What I find useful is installing an executable called ‘elinks’ (available on oracle linux via yum), and then read the README.html in this text based browser:

$ elinks README.html

Result:

                                             Oracle® Database Patch % psuBug % - Database Release Update % product_version % (1/12)
                                                       Go to primary content

   Patch 28655784 - Database Release Update 18.4.0.0.181016

   ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────

   The script content on this page is for navigation purposes only and does not alter the content in any way.

                                                          Oracle® Database

   Patch 28655784 - Database Release Update 18.4.0.0.181016

   This document is accurate at the time of release. For any changes and additional information regarding Database Release Update
   18.4.0.0.181016, see these related documents that are available at My Oracle Support (http://support.oracle.com/):

     * Document 2433586.1 Oracle DB/GI/OJVM Update/Revision R18 Oct 2018 Known Issues

   This document includes the following sections:

     * Section 1, "Patch Information"

     * Section 2, "Prerequisites"

     * Section 3, "Installation"

     * Section 4, "Deinstallation"

     * Section 5, "Known Issues"

     * Section 6, "References"

     * Section 7, "Bugs Fixed by This Patch"

     * Section 8, "Documentation Accessibility"

1 Patch Information

   Database Release Update 18.4.0.0.181016 patches are cumulative. That is, the content of all previous Database bundles is
   included in the latest Database bundle patch.

   To install the Database Release Update 18.4.0.0.181016 patch, the Oracle home must have the 18.1 Database installed.
http://support.oracle.com/                                                                                                 [------]

The first thing I do, is obtain information about this patch. Based on my experience, very carefully examine the readme. It contains vital information about the patch, but I found inconsistencies in it in the past. I got the impression the readme’s of patches that are created with a certain interval are simply copied from a previous version and then corrected by hand, and things might be entered wrong or are forgotten and not touched, leaving wrong information.

opatch lspatches on an unzipped patch

Luckily, a lot of information in the readme is actually stored in the metadata of the patch. You can query the metadata of the patch to be applied using opatch lspatches by pointing it to the patch directory:
(in this example I am querying patch 28655784, which is RU 18.4, and my database oracle home is version 18.3)

$ /u01/app/oracle/product/18.3.0.0/dbhome_1/OPatch/opatch lspatches 28655784/
patch_id:28655784
unique_patch_id:22509982
date_of_patch:8 Oct 2018, 21:27:28 hrs PST8PDT
patch_description:Database Release Update : 18.4.0.0.181016 (28655784)
component:oracle.rdbms.rsf.ic,18.0.0.0.0,optional; oracle.oracore.rsf,18.0.0.0.0,optional; oracle.ctx.atg,18.0.0.0.0,optional; oracle.rdbms.rman,18.0.0.0.0,optional; oracle.rdbms.rsf,18.0.0.0.0,optional; oracle.sdo.locator.jrf,18.0.0.0.0,optional; ....
platform:226,Linux x86-64
executable:ORACLE_HOME/lib/libclntsh.so.18.1; ORACLE_HOME/lib/libasmclntsh18.so; ORACLE_HOME/lib/libskgxp11.so; ORACLE_HOME/lib/libskgxp18.so; ORACLE_HOME/lib/libsqlplus.so; ORACLE_HOME/bin/oracle; ...
instance_shutdown:true
online_rac_installable:true
patch_type:singleton
product_family:db
auto:false
bug:28571483, TRACKING BUG TO REGRESS ALL BLR/CIS OF 27502420
...

(edited for brevity)
Line 2: patch_id: patch number
Line 5: patch_description: description </p />
</p></div>

    	  	<div class=

Super Lock an Oracle Database

I started this blog post a few weeks ago and kept adding to it from time to time but I have been incredibly busy helping people secure data in their Oracle databases that it has taken a long time to....[Read More]

Posted by Pete On 14/11/18 At 02:20 PM

March 2019 – New Webinar Dates Announced for “Oracle Indexing Internals and Best Practices”

I’m very excited to announce two new Webinar events for my acclaimed “Oracle Indexing Internals and Best Practices” training event, running in March 2019 !! For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page. The webinars will run for 4 hours each day, spanning a full week period (Monday […]

Index Splits – 3

This is stored only for reference, and in case anyone wants to wade through the details. It’s the redo log dump from the 90/10 index leaf block split test from the previous blog posts running on 11.2.0.4 on Linux. The first part is the full block dump, the second part is an extract of the Record and Change vector headings with the embedded opcode (opc:) for the undo records in the redo vectors, and a tiny note of what each change vector is doing.

Full dump


*** 2018-11-02 19:16:45.844
*** SESSION ID:(244.47) 2018-11-02 19:16:45.844
*** CLIENT ID:() 2018-11-02 19:16:45.844
*** SERVICE NAME:(SYS$USERS) 2018-11-02 19:16:45.844
*** MODULE NAME:(MyModule) 2018-11-02 19:16:45.844
*** ACTION NAME:(MyAction) 2018-11-02 19:16:45.844
 
----- begin tree dump
branch: 0x140008b 20971659 (0: nrow: 2, level: 1)
   leaf: 0x140008f 20971663 (-1: nrow: 147 rrow: 147)
   leaf: 0x140008c 20971660 (0: nrow: 145 rrow: 145)
----- end tree dump
----- begin tree dump
branch: 0x140008b 20971659 (0: nrow: 3, level: 1)
   leaf: 0x140008f 20971663 (-1: nrow: 147 rrow: 147)
   leaf: 0x140008c 20971660 (0: nrow: 145 rrow: 145)
   leaf: 0x140008d 20971661 (1: nrow: 1 rrow: 1)
----- end tree dump
Initial buffer sizes: read 1024K, overflow 832K, change 805K
 
DUMP OF REDO FROM FILE '/u01/app/oracle/oradata/TEST/onlinelog/o1_mf_2_bfjt3tsl_.log'
 Opcodes *.*
 RBAs: 0x000000.00000000.0000 thru 0xffffffff.ffffffff.ffff
 SCNs: scn: 0x0000.00000000 thru scn: 0xffff.ffffffff
 Times: creation thru eternity
 FILE HEADER:
        Compatibility Vsn = 186647552=0xb200400
        Db ID=2124268929=0x7e9dc581, Db Name='TEST'
        Activation ID=2124229505=0x7e9d2b81
        Control Seq=28320068=0x1b02144, File size=204800=0x32000
        File Number=2, Blksiz=512, File Type=2 LOG
 descrip:"Thread 0001, Seq# 0000012654, SCN 0x0b860d004016-0xffffffffffff"
 thread: 1 nab: 0xffffffff seq: 0x0000316e hws: 0x1 eot: 1 dis: 0
 resetlogs count: 0x313da101 scn: 0x0000.00000001 (1)
 prev resetlogs count: 0x0 scn: 0x0000.00000000
 Low  scn: 0x0b86.0d004016 (12670371643414) 11/02/2018 19:16:45
 Next scn: 0xffff.ffffffff 01/01/1988 00:00:00
 Enabled scn: 0x0000.00000001 (1) 09/14/2013 14:28:50
 Thread closed scn: 0x0b86.0d004016 (12670371643414) 11/02/2018 19:16:45
 Disk cksum: 0xfc42 Calc cksum: 0xfc42
 Terminal recovery stop scn: 0x0000.00000000
 Terminal recovery  01/01/1988 00:00:00
 Most recent redo scn: 0x0000.00000000
 Largest LWN: 0 blocks
 End-of-redo stream : No
 Unprotected mode
 Miscellaneous flags: 0x800000
 Thread internal enable indicator: thr: 0, seq: 0 scn: 0x0000.00000000
 Zero blocks: 0
 Format ID is 2
 redo log key is 16409c48687eb1bbc3e4a4d820a6f7e5
 redo log key flag is d
 Enabled redo threads: 1 
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000002.0010 LEN: 0x0074 VLD: 0x05
SCN: 0x0b86.0d004016 SUBSCN:  1 11/02/2018 19:16:45
(LWN RBA: 0x00316e.00000002.0010 LEN: 0038 NST: 0001 SCN: 0x0b86.0d004016)
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d003f20 SEQ:2 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:  0x0b86.0d004016 ver: 0x01 opt: 0x02, entries follow...
  itli: 2  flg: 2  scn: 0x0b86.0d003f20
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000002.0084 LEN: 0x0144 VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  2 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d003fe6 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000d sqn: 0x00009d48 flg: 0x0412 siz: 136 fbi: 0
            uba: 0x00c05593.2254.1d    pxid:  0x0000.000.00000000
CHANGE #2 TYP:0 CLS:76 AFN:3 DBA:0x00c05593 OBJ:4294967295 SCN:0x0b86.0d003fe5 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 2818 flg: 0x0012 seq: 0x2254 rec: 0x1d
            xid:  0x001e.00d.00009d48  
ktubl redo: slt: 13 rci: 0 opc: 10.21 [objn: 350367 objd: 350367 tsn: 7]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c05593.2254.1b 
prev ctl max cmt scn:  0x0b86.0d003da4  prev tx cmt scn:  0x0b86.0d003da5 
txn start scn:  0xffff.ffffffff  logon user: 62  prev brb: 0  prev bcl: 0 BuExt idx: 0 flg2: 0
index general undo (branch) operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0019.01d.00009119 uba: 0x00c1702b.1ea1.02
                      flg: CB--    lkc:  0     scn: 0x0b86.0d003e8f
Dump kdige : block dba :0x0140008c, seghdr dba: 0x0140008a
unlock block
(1):  01
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.6 ENC:0 RBL:0
index redo (kdxlok):  lock block, count=2
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x001e.00d.00009d48    uba: 0x00c05593.2254.1d
lock itl 1
operation = 0x5, pre-split
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000002.01c8 LEN: 0x20a4 VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  3 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000d sqn: 0x00000000 flg: 0x000a siz: 8148 fbi: 255
            uba: 0x00c05595.2254.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:76 AFN:3 DBA:0x00c05595 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 8148 spc: 2680 flg: 0x000a seq: 0x2254 rec: 0x01
            xid:  0x001e.00d.00009d48  
ktubu redo: slt: 13 rci: 0 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00c05593
index general undo (branch) operations
KTB Redo 
op: 0x05  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: R  itc: 2
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x001e.00d.00009d48  0x00c05593.2254.1d  ----    1  fsc 0x0000.00000000
0x02   0x001d.012.0000a9d7  0x00c04b88.256f.10  C---    0  scn 0x0b86.0d003f20
Dump kdige : block dba :0x0140008c, seghdr dba: 0x0140008a
restore block before image
(8032): 
 00 01 85 02 01 00 00 00 91 00 46 01 5c 01 16 00 00 00 00 00 00 00 00 00 8f
 00 40 01 06 00 00 00 60 1f 00 00 2b 1f f6 1e c1 1e 8c 1e 57 1e 22 1e ed 1d
 b8 1d 83 1d 4e 1d 19 1d e4 1c af 1c 7a 1c 45 1c 10 1c db 1b a6 1b 71 1b 3c
 1b 07 1b d2 1a 9d 1a 68 1a 33 1a fe 19 c9 19 94 19 5f 19 2a 19 f5 18 c0 18
 8b 18 56 18 21 18 ec 17 b7 17 82 17 4d 17 18 17 e3 16 ae 16 79 16 44 16 0f
 16 da 15 a5 15 70 15 3b 15 06 15 d1 14 9c 14 68 14 33 14 fe 13 c9 13 94 13
 5f 13 2a 13 f5 12 c0 12 8b 12 56 12 21 12 ec 11 b7 11 82 11 4d 11 18 11 e3
 10 ae 10 79 10 44 10 0f 10 da 0f a5 0f 70 0f 3b 0f 06 0f d1 0e 9c 0e 67 0e
 32 0e fd 0d c8 0d 93 0d 5e 0d 29 0d f4 0c bf 0c 8a 0c 55 0c 20 0c eb 0b b6
 0b 81 0b 4c 0b 17 0b e2 0a ad 0a 78 0a 43 0a 0e 0a d9 09 a4 09 6f 09 3a 09
 05 09 d0 08 9b 08 66 08 31 08 fc 07 c7 07 92 07 5d 07 28 07 f3 06 be 06 89
 06 54 06 1f 06 ea 05 b5 05 80 05 4b 05 16 05 e1 04 ac 04 77 04 42 04 0d 04
 d8 03 a3 03 6e 03 39 03 04 03 cf 02 9a 02 65 02 30 02 fb 01 c6 01 91 01 5c
 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 01 40 00 85 00 05 03 c2 03 5d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 85 00 04 03 c2 03 5c 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 85 00 03 03 c2 03 5b 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 85 00 02 03 c2 03 5a 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 85 00 01 03 c2 03 59 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 85 00 00 03 c2 03 58
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 46 03
 c2 03 57 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86
 00 45 03 c2 03 56 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 86 00 44 03 c2 03 55 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 86 00 43 03 c2 03 54 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 86 00 42 03 c2 03 53 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 86 00 41 03 c2 03 52 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 40 03 c2 03 51 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 3f 03 c2 03 50 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 3e 03 c2
 03 4f 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00
 3d 03 c2 03 4e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 86 00 3c 03 c2 03 4d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 86 00 3b 03 c2 03 4c 28 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 00 00 01 40 00 86 00 3a 03 c2 03 4b 28 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 00 00 01 40 00 86 00 39 03 c2 03 4a 28 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 38 03 c2 03 49 28 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 37 03 c2 03 48 28 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 36 03 c2 03
 47 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 35
 03 c2 03 46 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00
 86 00 34 03 c2 03 45 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00
 01 40 00 86 00 33 03 c2 03 44 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 86 00 32 03 c2 03 43 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 86 00 31 03 c2 03 42 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 86 00 30 03 c2 03 41 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 2f 03 c2 03 40 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 2e 03 c2 03 3f
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 2d 03
 c2 03 3e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86
 00 2c 03 c2 03 3d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 86 00 2b 03 c2 03 3c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 86 00 2a 03 c2 03 3b 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 86 00 29 03 c2 03 3a 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 86 00 28 03 c2 03 39 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 27 03 c2 03 38 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 26 03 c2 03 37 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 25 03 c2
 03 36 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00
 24 03 c2 03 35 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 86 00 23 03 c2 03 34 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 86 00 22 03 c2 03 33 28 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 00 00 01 40 00 86 00 21 03 c2 03 32 28 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 00 00 01 40 00 86 00 20 03 c2 03 31 28 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 1f 03 c2 03 30 28 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 1e 03 c2 03 2f 28 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 1d 03 c2 03
 2e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 1c
 03 c2 03 2d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00
 86 00 1b 03 c2 03 2c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00
 01 40 00 86 00 1a 03 c2 03 2b 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 86 00 19 03 c2 03 2a 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 86 00 18 03 c2 03 29 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 86 00 17 03 c2 03 28 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 16 03 c2 03 27 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 15 03 c2 03 26
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 14 03
 c2 03 25 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86
 00 13 03 c2 03 24 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 86 00 12 03 c2 03 23 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 86 00 11 03 c2 03 22 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 86 00 10 03 c2 03 21 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 86 00 0f 03 c2 03 20 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 0e 03 c2 03 1f 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 0d 03 c2 03 1e 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 0c 03 c2
 03 1d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00
 0b 03 c2 03 1c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 86 00 0a 03 c2 03 1b 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 86 00 09 03 c2 03 1a 28 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 00 00 01 40 00 86 00 08 03 c2 03 19 28 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 00 00 01 40 00 86 00 07 03 c2 03 18 28 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 06 03 c2 03 17 28 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 05 03 c2 03 16 28 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 04 03 c2 03
 15 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 86 00 03
 03 c2 03 14 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00
 86 00 02 03 c2 03 13 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00
 01 40 00 86 00 01 03 c2 03 12 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 86 00 00 03 c2 03 11 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 84 00 46 03 c2 03 10 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 84 00 45 03 c2 03 0f 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 44 03 c2 03 0e 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 43 03 c2 03 0d
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 42 03
 c2 03 0c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84
 00 41 03 c2 03 0b 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 84 00 40 03 c2 03 0a 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 84 00 3f 03 c2 03 09 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 84 00 3e 03 c2 03 08 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 84 00 3d 03 c2 03 07 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 3c 03 c2 03 06 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 3b 03 c2 03 05 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 3a 03 c2
 03 04 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00
 39 03 c2 03 03 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 84 00 38 03 c2 03 02 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 84 00 37 02 c2 03 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 84 00 36 03 c2 02 64 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 84 00 35 03 c2 02 63 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 84 00 34 03 c2 02 62 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 33 03 c2 02 61 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 32 03 c2 02 60
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 31 03
 c2 02 5f 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84
 00 30 03 c2 02 5e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 84 00 2f 03 c2 02 5d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 84 00 2e 03 c2 02 5c 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 84 00 2d 03 c2 02 5b 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 84 00 2c 03 c2 02 5a 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 2b 03 c2 02 59 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 2a 03 c2 02 58 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 29 03 c2
 02 57 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00
 28 03 c2 02 56 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 84 00 27 03 c2 02 55 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 84 00 26 03 c2 02 54 28 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 00 00 01 40 00 84 00 25 03 c2 02 53 28 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 00 00 01 40 00 84 00 24 03 c2 02 52 28 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 23 03 c2 02 51 28 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 22 03 c2 02 50 28 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 21 03 c2 02
 4f 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 20
 03 c2 02 4e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00
 84 00 1f 03 c2 02 4d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00
 01 40 00 84 00 1e 03 c2 02 4c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 84 00 1d 03 c2 02 4b 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 84 00 1c 03 c2 02 4a 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 00 00 01 40 00 84 00 1b 03 c2 02 49 28 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 1a 03 c2 02 48 28 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 19 03 c2 02 47
 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 18 03
 c2 02 46 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84
 00 17 03 c2 02 45 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01
 40 00 84 00 16 03 c2 02 44 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 00 00 01 40 00 84 00 15 03 c2 02 43 28 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 00 00 01 40 00 84 00 14 03 c2 02 42 28 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 00 00 01 40 00 84 00 13 03 c2 02 41 28 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 12 03 c2 02 40 28 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 11 03 c2 02 3f 28
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 10 03 c2
 02 3e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00
 0f 03 c2 02 3d 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40
 00 84 00 0e 03 c2 02 3c 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00
 00 01 40 00 84 00 0d 03 c2 02 3b 28 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 00 00 01 40 00 84 00 0c 03 c2 02 3a 28 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 00 00 01 40 00 84 00 0b 03 c2 02 39 28 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 0a 03 c2 02 38 28 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 09 03 c2 02 37 28 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 08 03 c2 02
 36 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00 84 00 07
 03 c2 02 35 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00 01 40 00
 84 00 06 03 c2 02 34 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 00 00
 01 40 00 84 00 05 03 c2 02 33 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 00 00 01 40 00 84 00 04 03 c2 02 32 28 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 00 00 01 40 00 84 00 03 03 c2 02 31 28 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:2 OP:10.9 ENC:0 RBL:0
index redo (kdxair): apply xat do to itl 1 (count=2)
KTB Redo 
op: 0x05  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: R  itc: 1
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x001e.00d.00009d48  0x00c05595.2254.01  -B--    1  fsc 0x0000.00000000
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000013.017c LEN: 0x0044 VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  3 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:8 AFN:5 DBA:0x01400088 OBJ:350367 SCN:0x0b86.0d003e8e SEQ:3 OP:13.22 ENC:0 RBL:0
Redo on Level1 Bitmap Block
Redo for state change
Len: 1 Offset: 4 newstate: 140243567116289
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000013.01c0 LEN: 0x01ac VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  4 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:2 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x000d sqn: 0x00000000 flg: 0x000a siz: 120 fbi: 0
            uba: 0x00c05596.2254.01    pxid:  0x0000.000.00000000
CHANGE #2 TYP:1 CLS:76 AFN:3 DBA:0x00c05596 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 120 spc: 0 flg: 0x000a seq: 0x2254 rec: 0x01
            xid:  0x001e.00d.00009d48  
ktubu redo: slt: 13 rci: 0 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00c05595
index general undo (branch) operations
KTB Redo 
op: 0x05  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: R  itc: 2
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
Dump kdige : block dba :0x0140008d, seghdr dba: 0x0140008a
make leaf block empty
(2):  01 00
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008d OBJ:350367 SCN:0x0b86.0d003e8e SEQ:2 OP:10.8 ENC:0 RBL:0
index redo (kdxlne): (count=4) init header of newly allocated leaf block
KTB Redo 
op: 0x05  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: R  itc: 2
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x001e.00d.00009d48  0x00c05596.2254.01  -B--    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
kdxlnitl = 1
kdxlnnco = 2
kdxlndsz = 6
kdxlncol = 246
kdxlnflg = 0
kdxlnnxt = 0x0
kdxlnprv = 0x140008c
new block has 1 rows
dumping row index
Dump of memory from 0x00007F8D3FC10078 to 0x00007F8D3FC1007C
7F8D3FC10070                   1F5D1F26                   [&.].]    
dumping rows
Dump of memory from 0x00007F8D3FC1007C to 0x00007F8D3FC100B1
7F8D3FC10070                            40010101              [...@]
7F8D3FC10080 06008500 5E03C203 78787828 78787878  [.......^(xxxxxxx]
7F8D3FC10090 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC100B0 30303078                             [x000]            
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000014.017c LEN: 0x0048 VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  4 11/02/2018 19:16:45
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x0140008b OBJ:350367 SCN:0x0b86.0d003e8f SEQ:2 OP:4.1 ENC:0 RBL:0
Block cleanout record, scn:  0x0b86.0d004016 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0b86.0d003e8f
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000014.01c4 LEN: 0x00e0 VLD: 0x01
SCN: 0x0b86.0d004016 SUBSCN:  5 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:76 AFN:3 DBA:0x00c05596 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 84 spc: 8028 flg: 0x0022 seq: 0x2254 rec: 0x02
            xid:  0x001e.00d.00009d48  
ktubu redo: slt: 13 rci: 1 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index general undo (branch) operations
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x0019.01d.00009119 uba: 0x00c17029.1ea1.01
                      flg: CB--    lkc:  0     scn: 0x0b86.0d003e8f
Dump kdige : block dba :0x0140008b, seghdr dba: 0x0140008a
branch block row purge
(4):  01 00 01 00
CHANGE #2 TYP:0 CLS:1 AFN:5 DBA:0x0140008b OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.15 ENC:0 RBL:0
index redo (kdxbin) :  insert branch block row, count=3
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x001e.00d.00009d48    uba: 0x00c05596.2254.02
REDO itl: 1 insert into slot 1, child dba 0x140008d
new key : (5):  03 c2 03 5e fe
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000015.00b4 LEN: 0x1fb0 VLD: 0x01
SCN: 0x0b86.0d004017 SUBSCN:  1 11/02/2018 19:16:45
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:3 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x000d sqn: 0x00009d48 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c05596.2254.02 ext: 14 spc: 7942 fbi: 0 
CHANGE #2 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:3 OP:10.8 ENC:0 RBL:0
index redo (kdxlne): (count=4) init leaf block being split
zeroed lock count and free space, kdxlenxt = 0x140008d
new block has 145 rows
dumping row index
Dump of memory from 0x00007F8D3FC10028 to 0x00007F8D3FC1014C
7F8D3FC10020                   00370000 00A5006E          [..7.n...]
7F8D3FC10030 011300DC 0181014A 01EF01B8 025D0226  [....J.......&.].]
7F8D3FC10040 02CB0294 03390302 03A70370 041503DE  [......9.p.......]
7F8D3FC10050 0483044C 04F104BA 055F0528 05CD0596  [L.......(._.....]
7F8D3FC10060 063B0604 06A90672 071706E0 0785074E  [..;.r.......N...]
7F8D3FC10070 07F307BC 0861082A 08CF0898 093D0906  [....*.a.......=.]
7F8D3FC10080 09AB0974 0A1909E2 0A870A50 0AF50ABE  [t.......P.......]
7F8D3FC10090 0B620B2C 0BD00B99 0C3E0C07 0CAC0C75  [,.b.......>.u...]
7F8D3FC100A0 0D1A0CE3 0D880D51 0DF60DBF 0E640E2D  [....Q.......-.d.]
7F8D3FC100B0 0ED20E9B 0F400F09 0FAE0F77 101C0FE5  [......@.w.......]
7F8D3FC100C0 108A1053 10F810C1 1166112F 11D4119D  [S......./.f.....]
7F8D3FC100D0 1242120B 12B01279 131E12E7 138C1355  [..B.y.......U...]
7F8D3FC100E0 13FA13C3 14681431 14D6149F 1544150D  [....1.h.......D.]
7F8D3FC100F0 15B2157B 162015E9 168E1657 16FC16C5  [{..... .W.......]
7F8D3FC10100 176A1733 17D817A1 1846180F 18B4187D  [3.j.......F.}...]
7F8D3FC10110 192218EB 19901959 19FE19C7 1A6C1A35  [..".Y.......5.l.]
7F8D3FC10120 1ADA1AA3 1B481B11 1BB61B7F 1C241BED  [......H.......$.]
7F8D3FC10130 1C921C5B 1D001CC9 1D6E1D37 1DDC1DA5  [[.......7.n.....]
7F8D3FC10140 1E4A1E13 1EB81E81 1F261EEF           [..J.......&.]    
dumping rows
Dump of memory from 0x00007F8D3FC1014C to 0x00007F8D3FC11F50
7F8D3FC10140                            40010000              [...@]
7F8D3FC10150 03008400 3102C203 78787828 78787878  [.......1(xxxxxxx]
7F8D3FC10160 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10180 01000078 00840040 02C20304 78782832  [x...@.......2(xx]
7F8D3FC10190 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC101B0 78787878 00007878 84004001 C2030500  [xxxxxx...@......]
7F8D3FC101C0 78283302 78787878 78787878 78787878  [.3(xxxxxxxxxxxxx]
7F8D3FC101D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC101E0 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC101F0 03060084 283402C2 78787878 78787878  [......4(xxxxxxxx]
7F8D3FC10200 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10220 40010000 07008400 3502C203 78787828  [...@.......5(xxx]
7F8D3FC10230 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10250 78787878 01000078 00840040 02C20308  [xxxxx...@.......]
7F8D3FC10260 78782836 78787878 78787878 78787878  [6(xxxxxxxxxxxxxx]
7F8D3FC10270 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10280 78787878 78787878 00007878 84004001  [xxxxxxxxxx...@..]
7F8D3FC10290 C2030900 78283702 78787878 78787878  [.....7(xxxxxxxxx]
7F8D3FC102A0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC102B0 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC102C0 00400100 030A0084 283802C2 78787878  [..@.......8(xxxx]
7F8D3FC102D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC102F0 78787878 40010000 0B008400 3902C203  [xxxx...@.......9]
7F8D3FC10300 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC10310 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10320 78787878 78787878 01000078 00840040  [xxxxxxxxx...@...]
7F8D3FC10330 02C2030C 7878283A 78787878 78787878  [....:(xxxxxxxxxx]
7F8D3FC10340 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10350 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC10360 84004001 C2030D00 78283B02 78787878  [.@.......;(xxxxx]
7F8D3FC10370 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10390 00787878 00400100 030E0084 283C02C2  [xxx...@.......<(]
7F8D3FC103A0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC103C0 78787878 78787878 40010000 0F008400  [xxxxxxxx...@....]
7F8D3FC103D0 3D02C203 78787828 78787878 78787878  [...=(xxxxxxxxxxx]
7F8D3FC103E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC103F0 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC10400 00840040 02C20310 7878283E 78787878  [@.......>(xxxxxx]
7F8D3FC10410 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10430 00007878 84004001 C2031100 78283F02  [xx...@.......?(x]
7F8D3FC10440 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10460 78787878 00787878 00400100 03120084  [xxxxxxx...@.....]
7F8D3FC10470 284002C2 78787878 78787878 78787878  [..@(xxxxxxxxxxxx]
7F8D3FC10480 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10490 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC104A0 13008400 4102C203 78787828 78787878  [.......A(xxxxxxx]
7F8D3FC104B0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC104D0 01000078 00840040 02C20314 78782842  [x...@.......B(xx]
7F8D3FC104E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10500 78787878 00007878 84004001 C2031500  [xxxxxx...@......]
7F8D3FC10510 78284302 78787878 78787878 78787878  [.C(xxxxxxxxxxxxx]
7F8D3FC10520 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10530 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC10540 03160084 284402C2 78787878 78787878  [......D(xxxxxxxx]
7F8D3FC10550 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10570 40010000 17008400 4502C203 78787828  [...@.......E(xxx]
7F8D3FC10580 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC105A0 78787878 01000078 00840040 02C20318  [xxxxx...@.......]
7F8D3FC105B0 78782846 78787878 78787878 78787878  [F(xxxxxxxxxxxxxx]
7F8D3FC105C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC105D0 78787878 78787878 00007878 84004001  [xxxxxxxxxx...@..]
7F8D3FC105E0 C2031900 78284702 78787878 78787878  [.....G(xxxxxxxxx]
7F8D3FC105F0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10600 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC10610 00400100 031A0084 284802C2 78787878  [..@.......H(xxxx]
7F8D3FC10620 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10640 78787878 40010000 1B008400 4902C203  [xxxx...@.......I]
7F8D3FC10650 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC10660 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10670 78787878 78787878 01000078 00840040  [xxxxxxxxx...@...]
7F8D3FC10680 02C2031C 7878284A 78787878 78787878  [....J(xxxxxxxxxx]
7F8D3FC10690 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC106A0 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC106B0 84004001 C2031D00 78284B02 78787878  [.@.......K(xxxxx]
7F8D3FC106C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC106E0 00787878 00400100 031E0084 284C02C2  [xxx...@.......L(]
7F8D3FC106F0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10710 78787878 78787878 40010000 1F008400  [xxxxxxxx...@....]
7F8D3FC10720 4D02C203 78787828 78787878 78787878  [...M(xxxxxxxxxxx]
7F8D3FC10730 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10740 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC10750 00840040 02C20320 7878284E 78787878  [@... ...N(xxxxxx]
7F8D3FC10760 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10780 00007878 84004001 C2032100 78284F02  [xx...@...!...O(x]
7F8D3FC10790 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC107B0 78787878 00787878 00400100 03220084  [xxxxxxx...@...".]
7F8D3FC107C0 285002C2 78787878 78787878 78787878  [..P(xxxxxxxxxxxx]
7F8D3FC107D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC107E0 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC107F0 23008400 5102C203 78787828 78787878  [...#...Q(xxxxxxx]
7F8D3FC10800 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10820 01000078 00840040 02C20324 78782852  [x...@...$...R(xx]
7F8D3FC10830 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10850 78787878 00007878 84004001 C2032500  [xxxxxx...@...%..]
7F8D3FC10860 78285302 78787878 78787878 78787878  [.S(xxxxxxxxxxxxx]
7F8D3FC10870 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10880 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC10890 03260084 285402C2 78787878 78787878  [..&...T(xxxxxxxx]
7F8D3FC108A0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC108C0 40010000 27008400 5502C203 78787828  [...@...'...U(xxx]
7F8D3FC108D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC108F0 78787878 01000078 00840040 02C20328  [xxxxx...@...(...]
7F8D3FC10900 78782856 78787878 78787878 78787878  [V(xxxxxxxxxxxxxx]
7F8D3FC10910 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10920 78787878 78787878 00007878 84004001  [xxxxxxxxxx...@..]
7F8D3FC10930 C2032900 78285702 78787878 78787878  [.)...W(xxxxxxxxx]
7F8D3FC10940 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10950 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC10960 00400100 032A0084 285802C2 78787878  [..@...*...X(xxxx]
7F8D3FC10970 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10990 78787878 40010000 2B008400 5902C203  [xxxx...@...+...Y]
7F8D3FC109A0 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC109B0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC109C0 78787878 78787878 01000078 00840040  [xxxxxxxxx...@...]
7F8D3FC109D0 02C2032C 7878285A 78787878 78787878  [,...Z(xxxxxxxxxx]
7F8D3FC109E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC109F0 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC10A00 84004001 C2032D00 78285B02 78787878  [.@...-...[(xxxxx]
7F8D3FC10A10 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10A30 00787878 00400100 032E0084 285C02C2  [xxx...@.......\(]
7F8D3FC10A40 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10A60 78787878 78787878 40010000 2F008400  [xxxxxxxx...@.../]
7F8D3FC10A70 5D02C203 78787828 78787878 78787878  [...](xxxxxxxxxxx]
7F8D3FC10A80 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10A90 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC10AA0 00840040 02C20330 7878285E 78787878  [@...0...^(xxxxxx]
7F8D3FC10AB0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10AD0 00007878 84004001 C2033100 78285F02  [xx...@...1..._(x]
7F8D3FC10AE0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10B00 78787878 00787878 00400100 03320084  [xxxxxxx...@...2.]
7F8D3FC10B10 286002C2 78787878 78787878 78787878  [..`(xxxxxxxxxxxx]
7F8D3FC10B20 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10B30 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC10B40 33008400 6102C203 78787828 78787878  [...3...a(xxxxxxx]
7F8D3FC10B50 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10B70 01000078 00840040 02C20334 78782862  [x...@...4...b(xx]
7F8D3FC10B80 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10BA0 78787878 00007878 84004001 C2033500  [xxxxxx...@...5..]
7F8D3FC10BB0 78286302 78787878 78787878 78787878  [.c(xxxxxxxxxxxxx]
7F8D3FC10BC0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10BD0 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC10BE0 03360084 286402C2 78787878 78787878  [..6...d(xxxxxxxx]
7F8D3FC10BF0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10C10 40010000 37008400 2803C202 78787878  [...@...7...(xxxx]
7F8D3FC10C20 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10C40 78787878 40010000 38008400 0203C203  [xxxx...@...8....]
7F8D3FC10C50 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC10C60 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10C70 78787878 78787878 01000078 00840040  [xxxxxxxxx...@...]
7F8D3FC10C80 03C20339 78782803 78787878 78787878  [9....(xxxxxxxxxx]
7F8D3FC10C90 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10CA0 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC10CB0 84004001 C2033A00 78280403 78787878  [.@...:....(xxxxx]
7F8D3FC10CC0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10CE0 00787878 00400100 033B0084 280503C2  [xxx...@...;....(]
7F8D3FC10CF0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10D10 78787878 78787878 40010000 3C008400  [xxxxxxxx...@...<]
7F8D3FC10D20 0603C203 78787828 78787878 78787878  [....(xxxxxxxxxxx]
7F8D3FC10D30 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10D40 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC10D50 00840040 03C2033D 78782807 78787878  [@...=....(xxxxxx]
7F8D3FC10D60 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10D80 00007878 84004001 C2033E00 78280803  [xx...@...>....(x]
7F8D3FC10D90 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10DB0 78787878 00787878 00400100 033F0084  [xxxxxxx...@...?.]
7F8D3FC10DC0 280903C2 78787878 78787878 78787878  [...(xxxxxxxxxxxx]
7F8D3FC10DD0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10DE0 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC10DF0 40008400 0A03C203 78787828 78787878  [...@....(xxxxxxx]
7F8D3FC10E00 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10E20 01000078 00840040 03C20341 7878280B  [x...@...A....(xx]
7F8D3FC10E30 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10E50 78787878 00007878 84004001 C2034200  [xxxxxx...@...B..]
7F8D3FC10E60 78280C03 78787878 78787878 78787878  [..(xxxxxxxxxxxxx]
7F8D3FC10E70 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10E80 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC10E90 03430084 280D03C2 78787878 78787878  [..C....(xxxxxxxx]
7F8D3FC10EA0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10EC0 40010000 44008400 0E03C203 78787828  [...@...D....(xxx]
7F8D3FC10ED0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10EF0 78787878 01000078 00840040 03C20345  [xxxxx...@...E...]
7F8D3FC10F00 7878280F 78787878 78787878 78787878  [.(xxxxxxxxxxxxxx]
7F8D3FC10F10 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10F20 78787878 78787878 00007878 84004001  [xxxxxxxxxx...@..]
7F8D3FC10F30 C2034600 78281003 78787878 78787878  [.F....(xxxxxxxxx]
7F8D3FC10F40 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10F50 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC10F60 00400100 03000086 281103C2 78787878  [..@........(xxxx]
7F8D3FC10F70 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC10F90 78787878 40010000 01008600 1203C203  [xxxx...@........]
7F8D3FC10FA0 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC10FB0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10FC0 78787878 78787878 01000078 00860040  [xxxxxxxxx...@...]
7F8D3FC10FD0 03C20302 78782813 78787878 78787878  [.....(xxxxxxxxxx]
7F8D3FC10FE0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC10FF0 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC11000 86004001 C2030300 78281403 78787878  [.@........(xxxxx]
7F8D3FC11010 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11030 00787878 00400100 03040086 281503C2  [xxx...@........(]
7F8D3FC11040 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11060 78787878 78787878 40010000 05008600  [xxxxxxxx...@....]
7F8D3FC11070 1603C203 78787828 78787878 78787878  [....(xxxxxxxxxxx]
7F8D3FC11080 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11090 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC110A0 00860040 03C20306 78782817 78787878  [@........(xxxxxx]
7F8D3FC110B0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC110D0 00007878 86004001 C2030700 78281803  [xx...@........(x]
7F8D3FC110E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11100 78787878 00787878 00400100 03080086  [xxxxxxx...@.....]
7F8D3FC11110 281903C2 78787878 78787878 78787878  [...(xxxxxxxxxxxx]
7F8D3FC11120 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11130 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC11140 09008600 1A03C203 78787828 78787878  [........(xxxxxxx]
7F8D3FC11150 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11170 01000078 00860040 03C2030A 7878281B  [x...@........(xx]
7F8D3FC11180 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC111A0 78787878 00007878 86004001 C2030B00  [xxxxxx...@......]
7F8D3FC111B0 78281C03 78787878 78787878 78787878  [..(xxxxxxxxxxxxx]
7F8D3FC111C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC111D0 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC111E0 030C0086 281D03C2 78787878 78787878  [.......(xxxxxxxx]
7F8D3FC111F0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11210 40010000 0D008600 1E03C203 78787828  [...@........(xxx]
7F8D3FC11220 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11240 78787878 01000078 00860040 03C2030E  [xxxxx...@.......]
7F8D3FC11250 7878281F 78787878 78787878 78787878  [.(xxxxxxxxxxxxxx]
7F8D3FC11260 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11270 78787878 78787878 00007878 86004001  [xxxxxxxxxx...@..]
7F8D3FC11280 C2030F00 78282003 78787878 78787878  [..... (xxxxxxxxx]
7F8D3FC11290 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC112A0 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC112B0 00400100 03100086 282103C2 78787878  [..@.......!(xxxx]
7F8D3FC112C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC112E0 78787878 40010000 11008600 2203C203  [xxxx...@......."]
7F8D3FC112F0 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC11300 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11310 78787878 78787878 01000078 00860040  [xxxxxxxxx...@...]
7F8D3FC11320 03C20312 78782823 78787878 78787878  [....#(xxxxxxxxxx]
7F8D3FC11330 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11340 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC11350 86004001 C2031300 78282403 78787878  [.@.......$(xxxxx]
7F8D3FC11360 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11380 00787878 00400100 03140086 282503C2  [xxx...@.......%(]
7F8D3FC11390 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC113B0 78787878 78787878 40010000 15008600  [xxxxxxxx...@....]
7F8D3FC113C0 2603C203 78787828 78787878 78787878  [...&(xxxxxxxxxxx]
7F8D3FC113D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC113E0 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC113F0 00860040 03C20316 78782827 78787878  [@.......'(xxxxxx]
7F8D3FC11400 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11420 00007878 86004001 C2031700 78282803  [xx...@.......((x]
7F8D3FC11430 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11450 78787878 00787878 00400100 03180086  [xxxxxxx...@.....]
7F8D3FC11460 282903C2 78787878 78787878 78787878  [..)(xxxxxxxxxxxx]
7F8D3FC11470 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11480 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC11490 19008600 2A03C203 78787828 78787878  [.......*(xxxxxxx]
7F8D3FC114A0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC114C0 01000078 00860040 03C2031A 7878282B  [x...@.......+(xx]
7F8D3FC114D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC114F0 78787878 00007878 86004001 C2031B00  [xxxxxx...@......]
7F8D3FC11500 78282C03 78787878 78787878 78787878  [.,(xxxxxxxxxxxxx]
7F8D3FC11510 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11520 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC11530 031C0086 282D03C2 78787878 78787878  [......-(xxxxxxxx]
7F8D3FC11540 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11560 40010000 1D008600 2E03C203 78787828  [...@........(xxx]
7F8D3FC11570 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11590 78787878 01000078 00860040 03C2031E  [xxxxx...@.......]
7F8D3FC115A0 7878282F 78787878 78787878 78787878  [/(xxxxxxxxxxxxxx]
7F8D3FC115B0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC115C0 78787878 78787878 00007878 86004001  [xxxxxxxxxx...@..]
7F8D3FC115D0 C2031F00 78283003 78787878 78787878  [.....0(xxxxxxxxx]
7F8D3FC115E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC115F0 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC11600 00400100 03200086 283103C2 78787878  [..@... ...1(xxxx]
7F8D3FC11610 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11630 78787878 40010000 21008600 3203C203  [xxxx...@...!...2]
7F8D3FC11640 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC11650 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11660 78787878 78787878 01000078 00860040  [xxxxxxxxx...@...]
7F8D3FC11670 03C20322 78782833 78787878 78787878  ["...3(xxxxxxxxxx]
7F8D3FC11680 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11690 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC116A0 86004001 C2032300 78283403 78787878  [.@...#...4(xxxxx]
7F8D3FC116B0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC116D0 00787878 00400100 03240086 283503C2  [xxx...@...$...5(]
7F8D3FC116E0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11700 78787878 78787878 40010000 25008600  [xxxxxxxx...@...%]
7F8D3FC11710 3603C203 78787828 78787878 78787878  [...6(xxxxxxxxxxx]
7F8D3FC11720 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11730 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC11740 00860040 03C20326 78782837 78787878  [@...&...7(xxxxxx]
7F8D3FC11750 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11770 00007878 86004001 C2032700 78283803  [xx...@...'...8(x]
7F8D3FC11780 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC117A0 78787878 00787878 00400100 03280086  [xxxxxxx...@...(.]
7F8D3FC117B0 283903C2 78787878 78787878 78787878  [..9(xxxxxxxxxxxx]
7F8D3FC117C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC117D0 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC117E0 29008600 3A03C203 78787828 78787878  [...)...:(xxxxxxx]
7F8D3FC117F0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11810 01000078 00860040 03C2032A 7878283B  [x...@...*...;(xx]
7F8D3FC11820 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11840 78787878 00007878 86004001 C2032B00  [xxxxxx...@...+..]
7F8D3FC11850 78283C03 78787878 78787878 78787878  [.<(xxxxxxxxxxxxx]
7F8D3FC11860 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11870 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC11880 032C0086 283D03C2 78787878 78787878  [..,...=(xxxxxxxx]
7F8D3FC11890 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC118B0 40010000 2D008600 3E03C203 78787828  [...@...-...>(xxx]
7F8D3FC118C0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC118E0 78787878 01000078 00860040 03C2032E  [xxxxx...@.......]
7F8D3FC118F0 7878283F 78787878 78787878 78787878  [?(xxxxxxxxxxxxxx]
7F8D3FC11900 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11910 78787878 78787878 00007878 86004001  [xxxxxxxxxx...@..]
7F8D3FC11920 C2032F00 78284003 78787878 78787878  [./...@(xxxxxxxxx]
7F8D3FC11930 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11940 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC11950 00400100 03300086 284103C2 78787878  [..@...0...A(xxxx]
7F8D3FC11960 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11980 78787878 40010000 31008600 4203C203  [xxxx...@...1...B]
7F8D3FC11990 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC119A0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC119B0 78787878 78787878 01000078 00860040  [xxxxxxxxx...@...]
7F8D3FC119C0 03C20332 78782843 78787878 78787878  [2...C(xxxxxxxxxx]
7F8D3FC119D0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC119E0 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC119F0 86004001 C2033300 78284403 78787878  [.@...3...D(xxxxx]
7F8D3FC11A00 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11A20 00787878 00400100 03340086 284503C2  [xxx...@...4...E(]
7F8D3FC11A30 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11A50 78787878 78787878 40010000 35008600  [xxxxxxxx...@...5]
7F8D3FC11A60 4603C203 78787828 78787878 78787878  [...F(xxxxxxxxxxx]
7F8D3FC11A70 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11A80 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC11A90 00860040 03C20336 78782847 78787878  [@...6...G(xxxxxx]
7F8D3FC11AA0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11AC0 00007878 86004001 C2033700 78284803  [xx...@...7...H(x]
7F8D3FC11AD0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11AF0 78787878 00787878 00400100 03380086  [xxxxxxx...@...8.]
7F8D3FC11B00 284903C2 78787878 78787878 78787878  [..I(xxxxxxxxxxxx]
7F8D3FC11B10 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11B20 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC11B30 39008600 4A03C203 78787828 78787878  [...9...J(xxxxxxx]
7F8D3FC11B40 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11B60 01000078 00860040 03C2033A 7878284B  [x...@...:...K(xx]
7F8D3FC11B70 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11B90 78787878 00007878 86004001 C2033B00  [xxxxxx...@...;..]
7F8D3FC11BA0 78284C03 78787878 78787878 78787878  [.L(xxxxxxxxxxxxx]
7F8D3FC11BB0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11BC0 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC11BD0 033C0086 284D03C2 78787878 78787878  [..<...M(xxxxxxxx]
7F8D3FC11BE0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11C00 40010000 3D008600 4E03C203 78787828  [...@...=...N(xxx]
7F8D3FC11C10 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11C30 78787878 01000078 00860040 03C2033E  [xxxxx...@...>...]
7F8D3FC11C40 7878284F 78787878 78787878 78787878  [O(xxxxxxxxxxxxxx]
7F8D3FC11C50 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11C60 78787878 78787878 00007878 86004001  [xxxxxxxxxx...@..]
7F8D3FC11C70 C2033F00 78285003 78787878 78787878  [.?...P(xxxxxxxxx]
7F8D3FC11C80 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11C90 78787878 78787878 78787878 00787878  [xxxxxxxxxxxxxxx.]
7F8D3FC11CA0 00400100 03400086 285103C2 78787878  [..@...@...Q(xxxx]
7F8D3FC11CB0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11CD0 78787878 40010000 41008600 5203C203  [xxxx...@...A...R]
7F8D3FC11CE0 78787828 78787878 78787878 78787878  [(xxxxxxxxxxxxxxx]
7F8D3FC11CF0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11D00 78787878 78787878 01000078 00860040  [xxxxxxxxx...@...]
7F8D3FC11D10 03C20342 78782853 78787878 78787878  [B...S(xxxxxxxxxx]
7F8D3FC11D20 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11D30 78787878 78787878 78787878 00007878  [xxxxxxxxxxxxxx..]
7F8D3FC11D40 86004001 C2034300 78285403 78787878  [.@...C...T(xxxxx]
7F8D3FC11D50 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11D70 00787878 00400100 03440086 285503C2  [xxx...@...D...U(]
7F8D3FC11D80 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11DA0 78787878 78787878 40010000 45008600  [xxxxxxxx...@...E]
7F8D3FC11DB0 5603C203 78787828 78787878 78787878  [...V(xxxxxxxxxxx]
7F8D3FC11DC0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11DD0 78787878 78787878 78787878 01000078  [xxxxxxxxxxxxx...]
7F8D3FC11DE0 00860040 03C20346 78782857 78787878  [@...F...W(xxxxxx]
7F8D3FC11DF0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11E10 00007878 85004001 C2030000 78285803  [xx...@.......X(x]
7F8D3FC11E20 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11E40 78787878 00787878 00400100 03010085  [xxxxxxx...@.....]
7F8D3FC11E50 285903C2 78787878 78787878 78787878  [..Y(xxxxxxxxxxxx]
7F8D3FC11E60 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11E70 78787878 78787878 78787878 40010000  [xxxxxxxxxxxx...@]
7F8D3FC11E80 02008500 5A03C203 78787828 78787878  [.......Z(xxxxxxx]
7F8D3FC11E90 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11EB0 01000078 00850040 03C20303 7878285B  [x...@.......[(xx]
7F8D3FC11EC0 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
        Repeat 1 times
7F8D3FC11EE0 78787878 00007878 85004001 C2030400  [xxxxxx...@......]
7F8D3FC11EF0 78285C03 78787878 78787878 78787878  [.\(xxxxxxxxxxxxx]
7F8D3FC11F00 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
7F8D3FC11F10 78787878 78787878 00787878 00400100  [xxxxxxxxxxx...@.]
7F8D3FC11F20 03050085 285D03C2 78787878 78787878  [......](xxxxxxxx]
7F8D3FC11F30 78787878 78787878 78787878 78787878  [xxxxxxxxxxxxxxxx]
  Repeat 1 times
 
REDO RECORD - Thread:1 RBA: 0x00316e.00000025.0164 LEN: 0x0330 VLD: 0x09
SCN: 0x0b86.0d004018 SUBSCN:  1 11/02/2018 19:16:45
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x01400085 OBJ:350366 SCN:0x0b86.0d003f20 SEQ:2 OP:11.2 ENC:0 RBL:0
KTB Redo 
op: 0x11  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0019.004.00009104    uba: 0x00c1702a.1ea1.2a
Block cleanout record, scn:  0x0b86.0d004016 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0b86.0d003f1f
  itli: 2  flg: 2  scn: 0x0b86.0d003f20
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01400085  hdba: 0x01400082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 6(0x6) size/delt: 99
fb: --H-FL-- lb: 0x1  cc: 3
null: ---
col  0: [ 3]  c2 03 5e
col  1: [40]
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
col  2: [50]
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
CHANGE #2 TYP:0 CLS:65 AFN:3 DBA:0x00c000a8 OBJ:4294967295 SCN:0x0b86.0d003fdc SEQ:1 OP:5.2 ENC:0 RBL:0
ktudh redo: slt: 0x0004 sqn: 0x00009104 flg: 0x0012 siz: 136 fbi: 0
            uba: 0x00c1702a.1ea1.2a    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008d OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.5 ENC:0 RBL:0
index redo (kdxlre):  restore leaf row
KTB Redo 
op: 0x01  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: F  xid:  0x0019.004.00009104    uba: 0x00c1702a.1ea1.2b
REDO: SINGLE / -- / -- 
itl: 2, sno: 0, row size 55
CHANGE #4 TYP:0 CLS:65 AFN:3 DBA:0x00c000a8 OBJ:4294967295 SCN:0x0b86.0d004018 SEQ:1 OP:5.4 ENC:0 RBL:0
ktucm redo: slt: 0x0004 sqn: 0x00009104 srt: 0 sta: 9 flg: 0x2 ktucf redo: uba: 0x00c1702a.1ea1.2b ext: 60 spc: 2072 fbi: 0 
CHANGE #5 TYP:0 CLS:66 AFN:3 DBA:0x00c1702a OBJ:4294967295 SCN:0x0b86.0d003fdb SEQ:2 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 136 spc: 2324 flg: 0x0012 seq: 0x1ea1 rec: 0x2a
            xid:  0x0019.004.00009104  
ktubl redo: slt: 4 rci: 0 opc: 11.1 [objn: 350366 objd: 350366 tsn: 7]
Undo type:  Regular undo        Begin trans    Last buffer split:  No 
Temp Object:  No 
Tablespace Undo:  No 
             0x00000000  prev ctl uba: 0x00c1702a.1ea1.28 
prev ctl max cmt scn:  0x0b86.0d003d78  prev tx cmt scn:  0x0b86.0d003d88 
txn start scn:  0x0b86.0d004017  logon user: 62  prev brb: 12677159  prev bcl: 0 BuExt idx: 0 flg2: 0
KDO undo record:
KTB Redo 
op: 0x04  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: L  itl: xid:  0x001e.013.0000a096 uba: 0x00c05594.2254.32
                      flg: C---    lkc:  0     scn: 0x0b86.0d003f1f
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01400085  hdba: 0x01400082
itli: 1  ispac: 0  maxfr: 4858
tabn: 0 slot: 6(0x6)
CHANGE #6 TYP:0 CLS:66 AFN:3 DBA:0x00c1702a OBJ:4294967295 SCN:0x0b86.0d004018 SEQ:1 OP:5.1 ENC:0 RBL:0
ktudb redo: siz: 112 spc: 2186 flg: 0x0022 seq: 0x1ea1 rec: 0x2b
            xid:  0x0019.004.00009104  
ktubu redo: slt: 4 rci: 42 opc: 10.22 objn: 350367 objd: 350367 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No 
Tablespace Undo:  No 
             0x00000000
index undo for leaf key operations
KTB Redo 
op: 0x03  ver: 0x01  
compat bit: 4 (post-11) padding: 1
op: Z
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x140008a block=0x0140008d
(kdxlpu): purge leaf row
key :(45): 
 03 c2 03 5e 28 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78 78
END OF REDO DUMP
----- Redo read statistics for thread 1 -----
Read rate (ASYNC): 19Kb in 0.02s => 0.93 Mb/sec
Total redo bytes: 1023Kb Longest record: 8Kb, moves: 0/9 moved: 0Mb (0%)
Longest LWN: 19Kb, reads: 1 
Last redo scn: 0x0b86.0d004018 (12670371643416)
Change vector header moves = 0/22 (0%)
----------------------------------------------
[oracle@linux11 trace]$ 



Summary

REDO RECORD - Thread:1 RBA: 0x00316e.00000002.0010 LEN: 0x0074 VLD: 0x05
(LWN RBA: 0x00316e.00000002.0010 LEN: 0038 NST: 0001 SCN: 0x0b86.0d004016)
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d003f20 SEQ:2 OP:4.1 ENC:0 RBL:0
        Cleanout (the DBA is the high values leaf)

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000002.0084 LEN: 0x0144 VLD: 0x01
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d003fe6 SEQ:1 OP:5.2 ENC:0 RBL:0
        Update undo seg header 
                Start transaction (saves old transaction control data)

CHANGE #2 TYP:0 CLS:76 AFN:3 DBA:0x00c05593 OBJ:4294967295 SCN:0x0b86.0d003fe5 SEQ:2 OP:5.1 ENC:0 RBL:0
ktubl redo: slt: 13 rci: 0 opc: 10.21 [objn: 350367 objd: 350367 tsn: 7]
        index general undo (branch) operations
                Actually undo for high-values leaf.
                Detail "unlock block"

CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.6 ENC:0 RBL:0
        Lock block (the DBA is the high values leaf)

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000002.01c8 LEN: 0x20a4 VLD: 0x01
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.2 ENC:0 RBL:0
        Update undo segment header 
                Updates current undo block address
                set to record 1 of new block

CHANGE #2 TYP:1 CLS:76 AFN:3 DBA:0x00c05595 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.1 ENC:0 RBL:0
ktubu redo: slt: 13 rci: 0 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
        index general undo (branch) operations
                Actually undo for high-values leaf.
                Saves leaf block before image
                vector size 8,148 bytes

CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:2 OP:10.9 ENC:0 RBL:0
        Modify service ITL
                the DBA is the high values leaf block
                Set Flag to -B--, Lck to 1

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000013.017c LEN: 0x0044 VLD: 0x01
CHANGE #1 TYP:0 CLS:8 AFN:5 DBA:0x01400088 OBJ:350367 SCN:0x0b86.0d003e8e SEQ:3 OP:13.22 ENC:0 RBL:0
        Update L1 bitmap block 
                Space management for the index segment

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000013.01c0 LEN: 0x01ac VLD: 0x01
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:2 OP:5.2 ENC:0 RBL:0
        Update segment header
                Updates current undo block address
                set to record 1 of new block

CHANGE #2 TYP:1 CLS:76 AFN:3 DBA:0x00c05596 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:1 OP:5.1 ENC:0 RBL:0
ktubu redo: slt: 13 rci: 0 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
        index general undo (branch) operations
                Actually undo for new index leaf block
                Make leaf block empty.

CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008d OBJ:350367 SCN:0x0b86.0d003e8e SEQ:2 OP:10.8 ENC:0 RBL:0
        init header of newly allocated leaf block
                the DBA is next block to current high
                Include ITL (with service set to B), and row(s)

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000014.017c LEN: 0x0048 VLD: 0x01
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x0140008b OBJ:350367 SCN:0x0b86.0d003e8f SEQ:2 OP:4.1 ENC:0 RBL:0
        Block cleanout 
                the DBA is the root block
                set the SCN in the service ITL

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000014.01c4 LEN: 0x00e0 VLD: 0x01
CHANGE #1 TYP:0 CLS:76 AFN:3 DBA:0x00c05596 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:2 OP:5.1 ENC:0 RBL:0
kitubu redo: slt: 13 rci: 1 opc: 10.21 objn: 350367 objd: 350367 tsn: 7
        index general undo (branch) operations
                Modify root block
                Replace service ITL entry
                Purge a row

CHANGE #2 TYP:0 CLS:1 AFN:5 DBA:0x0140008b OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.15 ENC:0 RBL:0
        Insert branch block row
                The DBA is the root block
                inserts pointer to new high value block

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000015.00b4 LEN: 0x1fb0 VLD: 0x01
CHANGE #1 TYP:0 CLS:75 AFN:3 DBA:0x00c00170 OBJ:4294967295 SCN:0x0b86.0d004016 SEQ:3 OP:5.4 ENC:0 RBL:0
        Commit transaction
                The recursive transaction for the split

CHANGE #2 TYP:0 CLS:1 AFN:5 DBA:0x0140008c OBJ:350367 SCN:0x0b86.0d004016 SEQ:3 OP:10.8 ENC:0 RBL:0
        init leaf block being split
                the DBA is current high values
                writes most of a block image to disc 

------

REDO RECORD - Thread:1 RBA: 0x00316e.00000025.0164 LEN: 0x0330 VLD: 0x09
CHANGE #1 TYP:2 CLS:1 AFN:5 DBA:0x01400085 OBJ:350366 SCN:0x0b86.0d003f20 SEQ:2 OP:11.2 ENC:0 RBL:0
        Insert row piece
                (to table)
                cleans out current ITL entries

CHANGE #2 TYP:0 CLS:65 AFN:3 DBA:0x00c000a8 OBJ:4294967295 SCN:0x0b86.0d003fdc SEQ:1 OP:5.2 ENC:0 RBL:0
        start transaction 
                new seg header, and transaction table slot

CHANGE #3 TYP:0 CLS:1 AFN:5 DBA:0x0140008d OBJ:350367 SCN:0x0b86.0d004016 SEQ:1 OP:10.5 ENC:0 RBL:0
        restore leaf row
                This is the new high-values block
                Was the image of data from the previous block marked deleted ?

CHANGE #4 TYP:0 CLS:65 AFN:3 DBA:0x00c000a8 OBJ:4294967295 SCN:0x0b86.0d004018 SEQ:1 OP:5.4 ENC:0 RBL:0
        Commit

CHANGE #5 TYP:0 CLS:66 AFN:3 DBA:0x00c1702a OBJ:4294967295 SCN:0x0b86.0d003fdb SEQ:2 OP:5.1 ENC:0 RBL:0
ktubl redo: slt: 4 rci: 0 opc: 11.1 [objn: 350366 objd: 350366 tsn: 7]
        undo row operation
                Undo for a table row insert
                Also holds transaction table control data

CHANGE #6 TYP:0 CLS:66 AFN:3 DBA:0x00c1702a OBJ:4294967295 SCN:0x0b86.0d004018 SEQ:1 OP:5.1 ENC:0 RBL:0
ktubu redo: slt: 4 rci: 42 opc: 10.22 objn: 350367 objd: 350367 tsn: 7
        undo leaf key operation
                Purge the new row from the new leaf block

One detail to note – the sys-recursive transaction operates in “classic” redo style – i.e. one change at a time in separate records in the order of activity; then the user transaction runs in the “modern” redo style using private redo to batch the forward and backward change vectors.