Does OTLT (One true large table) slow down the query performance due to locks or any other reason ,
when it is LEFT JOINED or used in scalar sub-queries , multiple times as follows?
Does splitting OTLT into separate table, provides better performance?
CREATE TABLE AUTO
(
ID NUMBER ,
TYPE VARCHAR2(10),
NAME VARCHAR2(10)
CONSTRAINT PK_AUTO PRIMARY KEY (ID)
)
SELECT * FROM AUTO;
ID TYPE NAME
1 CAR FORD
2 CAR DODGE
3 CAR FIAT
4 CAR BENZ
5 CAR BMW
6 SUV JEEP
7 SUV VOLVO
8 SUV TOYOTA
9 SUV KIA
10 TRK OSKOSH
11 TRK DAIMLER
12 TRK MACK
13 TRK HINO
CREATE TABLE STOCK
(
ID NUMBER ,
LOCATION VARCHAR2(100),
CAR_ID NUMBER,
SUV_ID NUMBER,
TRK_ID NUMBER
CONSTRAINT PK_STOCK PRIMARY KEY (ID)
);
ALTER TABLE STOCK ADD CONSTRAINT FK_STOCK_AUTO_CAR FOREIGN KEY (CAR_ID) REFERENCES AUTO(ID);
ALTER TABLE STOCK ADD CONSTRAINT FK_STOCK_AUTO_SUV FOREIGN KEY (SUV_ID) REFERENCES AUTO(ID);
ALTER TABLE STOCK ADD CONSTRAINT FK_STOCK_AUTO_TRK FOREIGN KEY (TRK_ID) REFERENCES AUTO(ID);
CREATE INDEX IDX_FK_STOCK_AUTO_CAR ON STOCK(CAR_ID);
CREATE INDEX IDX_FK_STOCK_AUTO_SUV ON STOCK(SUV_ID);
CREATE INDEX IDX_FK_STOCK_AUTO_TRK ON STOCK(TRK_ID);
SELECT * FROM STOCK;
ID LOCATION CAR_ID SUV_ID TRK_ID
1 VA 1 8 13
2 MD NULL 6 10
3 NC 8 NULL 5
SELECT
STK.LOCATION,
CAR.NAME CAR_NAME,
SUV.NAME SUV_NAME,
TRK.NAME TRK_NAME
FROM
STOCK STK
LEFT JOIN
AUTO CAR ON STK.CAR_ID = CAR.ID
LEFT JOIN
AUTO SUV ON SUV.SUV_ID = SUV.ID
LEFT JOIN
AUTO TRK ON TRK.TRK_ID = TRK.ID
WHERE STK.LOCATION ='VA'; --------------------OR-----------------------------
SELECT
STK.LOCATION,
(SELECT CAR.NAME FROM AUTO CAR WHERE STK.CAR_ID = CAR.ID) CAR_NAME,
(SELECT SUV.NAME FROM AUTO SUV WHERE STK.SUV_ID = SUV.ID) SUV_NAME,
(SELECT TRK.NAME FROM AUTO TRK WHERE TRK.TRK_ID = TRK.ID) TRK_NAME,
WHERE STK.LOCATION ='VA';
----------------
LOCATION CAR_NAME SUV_NAME TRK_NAME
VA FORD TOYOTA HINO
due to locks or any other reasonRepeat after me:
Queries in Oracle Database acquire
NO data locks.
So this makes no difference whichever design you use.
Does splitting OTLT into separate table, provides better performance?Maybe. Splitting one table into many has a couple of advantages:
- The tables are smaller, so full scanning a table has less data to read => faster
- In general it's easier for the optimizer to estimate how many rows it'll get from each table => better plans => faster
So separate lookup tables are more likely to give faster queries. In practice if the plan has efficient index lookups there may be little/no difference.
I wouldn't really class this example as OTLT though. It's more an example of a supertype/subtype relationship. You almost certainly want the supertype auto table, possibly with separate child tables to store specific details for Cars, SUVs, etc.
And rather than separate columns for each type in the stock table, I'd add an auto type column instead:
CREATE TABLE STOCK
(
ID NUMBER ,
LOCATION VARCHAR2(100),
AUTO_ID NUMBER,
AUTO_TYPE NUMBER,
CONSTRAINT PK_STOCK PRIMARY KEY (ID)
);
Then you can join to the auto table once.
If you need the columnar format the output, you can always use PIVOT
https://blogs.oracle.com/sql/how-to-convert-rows-to-columns-and-back-again-with-sql-aka-pivot-and-unpivot