0

Below are the details of view and queries. What can be done? If you guys need the exec plan and view definitions, I will post it here.

SQL> desc indexes_view
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 FILE_NAME                                          VARCHAR2(513)
 OBJECT_ID                                          NUMBER
 OBJECT_NAME                               NOT NULL VARCHAR2(128)
 OWNER                                     NOT NULL VARCHAR2(128)

SQL> select count(*) from containers(indexes_view);

  COUNT(*)
----------
      9310

Elapsed: 00:00:02.09

SQL> select count(*) from containers(indexes_view) WHERE file_name='/OracleHome/LargeFile.dbf';

  COUNT(*)
----------
       492

Elapsed: 00:10:22.75
Phenomenal 4
  • 27
  • 9
  • There are ways to speed up things however: if you have an INDEX on a NOT NULL column Oracle will count the rows of the index instead of the table. In a proper relational model all tables have a primary key so the COUNT(*) will use the index of the primary key. – Sebastian Siemens Jan 03 '20 at 09:57
  • 2
    *"If you guys need the exec plan and view definitions"* Of course we need them. It is virtually impossible to look at a query and correctly guess why it's running slowly. Please read this post on [asking Oracle tuning questions](https://stackoverflow.com/a/34975420/146325). Also, it seems your using multi-tenant features so you should provide details of your set-up. Finally, is INDEXES_VIEW a view? If so, please post details (including explain plan) for the underlying query. – APC Jan 03 '20 at 10:17
  • 1
    @SebastianSiemens - my guess is INDEXES_VIEW is a view over data dictionary views. If I'm correct then building indexes and other tuning bullets are not appropriate. – APC Jan 03 '20 at 10:20
  • 1
    How many PDBs do you have?, and if you connect to a PDB and run the query with the WHERE clause, how long does it take? – gsalem Jan 03 '20 at 12:45

0 Answers0