When you think of a parameter that can help you out with star queries, this parameter is the STAR_TRANSFORMATION_ENABLED. According to the Oracle official documentation, it is described as the difference between a typical query and a star query, “A typical query in the access layer is a bond between the fact table and some number of dimension tables and is often referred to as a star query. In a star query, each dimension table is joined to the fact table using a primary key to foreign key join. Normally the dimension tables do not join to each other.”. Therefore, the process to enable STAR_TRANSFORMATION consists of two steps that are also described in the Oracle documentation:
- Create a bitmap index on each of the foreign key columns in the fact table or tables
- Set the initialization parameter STAR_TRANSFORMATION_ENABLED to TRUE.”.
So, in this post, I will first perform some tests without this parameter and I will be using an ordinary index then after with the parameter enabled and using a bitmap index as required in the process. It is also important to mention that the parameter STAR_TRANSFORMATION_ENABLED has 3 values { FALSE | TRUE | TEMP_DISABLE } where FALSE means that the parameter isn’t enabled, true means that the parameter will do a cost-based query transformation on the star query and TEMP_DISABLE is the same value of the TRUE option but it will ignore the use of temporary tables.
This will be the configuration of the databases and objects used in this post:
Tables: BRUNORS.TECHTABELA01 and BRUNORS.TECHTABELA02
PDB Database: PDB01
Let’s take a look on the objects to be created. Follow below the SQL to create the tables:
[oracle@vm1 scripts]$ cat tbs01.sql
CREATE TABLE BRUNORS.TECHTABELA01
(
ID_PKTB01 NUMBER NOT NULL
, DESCRIPTION VARCHAR2(40) NOT NULL
, SECTOR VARCHAR2(40) NOT NULL
, CONSTRAINT TECHTABELA01 PRIMARY KEY (ID_PKTB01) ENABLE );
[oracle@vm1 scripts]$ cat tbs02.sql
CREATE TABLE BRUNORS.TECHTABELA02
( ID_PKTB02 NUMBER NOT NULL,
ID_PKTB01 NUMBER NOT NULL,
FIELD VARCHAR2(40) NOT NULL
, CONSTRAINT TECHTABELA02_PK PRIMARY KEY (ID_PKTB02) ENABLE
,CONSTRAINT TECHTABELA02_FK FOREIGN KEY (ID_PKTB01) REFERENCES BRUNORS.TECHTABELA01 (ID_PKTB01) ENABLE);
Let’s connect to the PDB database and create the tables:
[oracle@scripts]$ sqlplus "/as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Tue May 12 06:16:15 2020
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB01 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> alter session set container=PDB01;
Session altered.
SQL> @tbs01.sql
Table created.
SQL> desc BRUNORS.TECHTABELA01
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_PKTB01 NOT NULL NUMBER
DESCRIPTION NOT NULL VARCHAR2(40)
SECTOR NOT NULL VARCHAR2(40)
SQL> @tbs02.sql
Table created.
SQL> desc BRUNORS.TECHTABELA02
Name Null? Type
----------------------------------------- -------- ----------------------------
ID_PKTB02 NOT NULL NUMBER
ID_PKTB01 NOT NULL NUMBER
FIELD NOT NULL VARCHAR2(40)
Now that the tables have been created, it’s time to populate them and run the statistics to get better results when displaying the output of the EXPLAIN PLAN:
SQL> select count(*) from BRUNORS.TECHTABELA01;
COUNT(*)
----------
0
SQL> @inserttbs01.sql
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from BRUNORS.TECHTABELA01;
COUNT(*)
----------
20
SQL> select count(*) from BRUNORS.TECHTABELA02;
COUNT(*)
----------
0
SQL> @inserttbs02.sql
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from BRUNORS.TECHTABELA02;
COUNT(*)
----------
18
SQL> SELECT 'ANALYZE TABLE BRUNORS.' || table_name || ' COMPUTE STATISTICS;' FROM all_tables where owner='BRUNORS';
'ANALYZETABLEBRUNORS.'||TABLE_NAME||'COMPUTESTATISTICS;'
--------------------------------------------------------------------------------
ANALYZE TABLE BRUNORS.T2 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.TECHTABELA01 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.TECHTABELA02 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.T1 COMPUTE STATISTICS;
SQL> ANALYZE TABLE BRUNORS.T2 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.TECHTABELA01 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.TECHTABELA02 COMPUTE STATISTICS;
ANALYZE TABLE BRUNORS.T1 COMPUTE STATISTICS;
Table analyzed.
SQL>
Table analyzed.
SQL>
Table analyzed.
SQL>
Table analyzed.
At this moment when the table is loaded and the STAR_TRANSFORMATION_ENABLED isn’t enabled, let’s create an ordinary index called BRUNORS.INX_TECHTABELA01 on the column ID_PKTB01 of the table TECHTABELA02 and right after seeing the results of an Explain Plan execution:
SQL> SQL> show parameters STAR_TRANSFORMATION_ENABLED NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ star_transformation_enabled string FALSE SQL> SQL> show con_name CON_NAME ------------------------------ PDB01 SQL> @?/rdbms/admin/utlxplan.sql Table created. SQL> create index BRUNORS.INX_TECHTABELA01 on BRUNORS.TECHTABELA02(ID_PKTB01); Index created. SQL> set pages 2000 set lines 2000 explain plan for select count(*) from BRUNORS.TECHTABELA01 tabela01 , BRUNORS.TECHTABELA02 tabela02 where tabela01.ID_PKTB01=tabela02.ID_PKTB01;SQL> SQL> 2 3 4 5 Explained. SQL> select * from table(dbms_Xplan.display) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2448368701 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | INDEX FULL SCAN| INX_TECHTABELA01 | 18 | 1 (0)| 00:00:01 | ----------------------------------------------------------------------------- 9 rows selected.
Pay attention at the ” INDEX FULL SCAN” operation used at this last execution and now let’s re-execute the Explain Plan with the star_transformation_enabled parameter configured to TRUE and instead of using the ordinary index BRUNORS.BITMAPINX_TECHTABELA01 created previously, I am going to create a bitmap index called BRUNORS.BITMAPINX_TECHTABELA01 on the same column ID_PKTB01 of the table BRUNORS.TECHTABELA02 as before:
SQL> alter system set star_transformation_enabled=TRUE scope=both; System altered. SQL> show parameters star_transformation_enabled NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ star_transformation_enabled string TRUE SQL> SQL> drop index BRUNORS.INX_TECHTABELA01; Index dropped. SQL> create bitmap index BRUNORS.BITMAPINX_TECHTABELA01 on BRUNORS.TECHTABELA02(ID_PKTB01); Index created. SQL> explain plan for select count(*) from BRUNORS.TECHTABELA01 tabela01 , BRUNORS.TECHTABELA02 tabela02 where tabela01.ID_PKTB01=tabela02.ID_PKTB01; 2 3 4 5 Explained. SQL> select * from table(dbms_Xplan.display) ; PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ Plan hash value: 2749853385 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 1 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | BITMAP CONVERSION COUNT | | 18 | 1 (0)| 00:00:01 | | 3 | BITMAP INDEX FAST FULL SCAN| BITMAPINX_TECHTABELA01 | | | | ------------------------------------------------------------------------------------------------ 10 rows selected.
On the second execution, you could see that the database met the criteria of having a bitmap index created on the foreign key column of the table BRUNORS.TECHTABELA02 and also the parameter STAR_TRANSFORMATION_ENABLED was configured to TRUE. Therefore, the star queries started to use an approach that consists of rewriting the executed SQL to retrieve only the essential queries and then getting a better improvement in performance. Furthermore, as you could see in the last explain plan execution the operation “Bitmap Conversion Count” was showed that it won’t be necessary for the query to convert the bitmap into ROWIDS for instance, meaning there is also some improvement in the performance.
Hi! I am Bruno, a Brazilian born and bred, and I am also a naturalized Swedish citizen. I am a former Oracle ACE and, to keep up with academic research, I am a Computer Scientist with an MSc in Data Science and another MSc in Software Engineering. I have over ten years of experience working with companies such as IBM, Epico Tech, and Playtech across three different countries (Brazil, Hungary, and Sweden), and I have joined projects remotely in many others. I am super excited to share my interests in Databases, Cybersecurity, Cloud, Data Science, Data Engineering, Big Data, AI, Programming, Software Engineering, and data in general.
(Continue reading)