SQL & PL/SQL

Hi,
Please advise for the solution. Thanks in advance.

The problem is that the queries returning data very very slow.

Let me explain further, There is one major table that is storing master data (Main Transaction) and its detail data (Child Records) and further detail of the detail data (Child Records of the Child Records) and so on sometimes upto 5 or 6 levels in the same ONE big table called "TRANSACTIONS_TABLE" table.

Therefore, mostly my quries are self joins like

SELECT /*+ FIRST_ROWS(10) */ JUDG.ID JUDG_ID,
SE.ID SE_ID,
CS.ID CS_ID,
CS.CS_DATE,
CS.CS_J_ID,
CS.CT_ID,
C.ID C_ID,
C.TRANSACTIOIN_TYPE_ID C_TRANSACTIOIN_TYPE_ID,
C.TRANSACTIOIN_NO,
C.TRANSACTIOIN_YEAR,
C.TRANSACTIOIN_DATE,
C.SUBJECT,
C.UNIT_ID,
(SELECT U.NAME_N FROM ORG U WHERE U.ID = C.UNIT_ID) PRC_UNIT_DESC,
C.E4 NO_AL_QAZYA_ID,
(
SELECT T.DESCRIPTION
FROM prc_charge_categories T
WHERE T.ID = TO_NUMBER(C.E4)
) NO_AL_QAZYA,
C.CREATED_BY
FROM TRANSACTIOINSACTION_TABLE C,TRANSACTIOINSACTION_TABLE SE, TRANSACTIOINSACTION_TABLE JUDG, DEF_TABLE CS
WHERE C.ID = SE.PARENT_ID
AND SE.ID = J.PARENT_ID
AND SE.TRANSACTIOIN_TYPE_ID = 63755
AND J.TRANSACTIOIN_TYPE_ID = 63424
AND J.E1 = '3'
AND CS.ID = TO_NUMBER(SE.E9);

23 rows selected in 48.672 seconds


I run the below query on this table

select transaction_type_id,count(*) total_rows
from transactions_table
group by transaction_type_id
order by 2 desc;

transact total_rows
114893 1192544
63755 774132
62270 682361
63424 633046
65220 515605
67728 457831
84928 378754
58806 296351
64240 277624
65244 275267
67224 152061
112610 123796
67434 116100
65824 88787
61369 76196
62269 74821
61740 67085
56448 56185
65703 55336
99601 54552
64423 49368
61478 43187
101689 39372
61652 38196
67136 27713
61364 25790
97961 23610
65380 19811
56353 18910
65618 18309
57041 16808
98137 15837
101794 14618
100127 14118
98916 13691
112735 13082
117349 11930
69334 11462
99064 10291
67811 10151
69402 9142
112753 8243
95950 7390
68902 6900
119474 6693
65659 5972
68360 5862
66198 5690
118340 5161
62012 4490
71088 4050
112040 3864
57315 3047
112031 2957
57289 2891
112039 2523
112038 2446
114254 2269
55550 2242
119926 2225
69650 2135
65033 1343
119078 811
69788 780
115133 774
116390 578
114968 549
65575 521
64290 412
120026 403
67388 359
116340 249
66847 228
98524 144
68214 138
120459 105
118777 92
79395 87
116039 79
65768 67
118376 58
120906 57
55848 52
58983 50
61195 48
68134 29
66645 24
55992 23
69037 21
70379 21
56674 20
112019 20
68911 18
70206 17
66028 16
114335 11
69555 7
55849 6
57122 6
65034 5
60496 5
57273 4
56286 4
55563 4
56972 3
55542 3
70654 3
55999 3
56721 3
57138 2
117802 2
57005 2
61058 2
116351 2
57096 2
57075 2
95136 1
115210 1
117601 1
68298 1
56939 1
65935 1
61739 1
65611 1
71249 1
57010 1
95024 1
68406 1
57097 1
122206 1
57062 1

132 rows selected in 5.266 seconds

Immediately, I again run this same query
132 rows selected in 5.079 seconds

then again
132 rows selected in 2.672 seconds

then again
132 rows selected in 4.438 seconds

then again
132 rows selected in 2.684 seconds


The structure of this table is

CREATE TABLE transactions_table
(
ID NUMBER NOT NULL,
PARENT_ID NUMBER,
TRANSACTION_TYPE_ID NUMBER NOT NULL,
TRANSACTION_YEAR NUMBER(4),
TRANSACTION_NO NUMBER(8),
TRANSACTION_DATE DATE NOT NULL,
C1 VARCHAR2(255 BYTE),
C2 VARCHAR2(255 BYTE),
C3 VARCHAR2(255 BYTE),
...................................
...................................
...................................
LONG_C1 VARCHAR2(4000 BYTE),
LONG_C2 VARCHAR2(4000 BYTE),
LONG_C3 VARCHAR2(4000 BYTE)
...................................
...................................
...................................
...................................
)
TABLESPACE TS16K_DATA
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 3144M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

CREATE UNIQUE INDEX MOJ.EVN_PK_I ON transactions_table
(ID)
NOLOGGING
TABLESPACE TS16K_INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 168M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

CREATE INDEX EVN_EVN_TYP_I ON transactions_table
(TRANSACTION_TYPE_ID)
NOLOGGING
TABLESPACE TS16K_INDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 136M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;


There are 150 COLUMNS in this table and out of which there are 33 columns data type is VARCHAR2(4000 BYTE) and others NUMBER datatype and some are VARCHAR2(255 BYTE)

As from above, the index on TRANSACTION_TYPE_ID is not unique and not bit map but a normal index.
Further there are no partitions for this table.

Many of the database views based on this table return results after several minutes.

* Around 5000 to 8000 rows are entered in this table on daily basis and is it feasible to create partitions and modify indexes to bit map and vice versa where ever required because already this table is having 54 Indexes including composite ones.

Please help and advise.