----------------------------------------------------------------------------------------------
select r.DOWNLOAD_ID,
       r.VERSION_ID,
       r.SOFT_ID,
       r.VERSION_NO,
       r.VERSION_DETAIL_ID,
       r.USER_ID,
       r.LAN_ID,
       r.DOWNLOAD_PERSON,
       r.DOWNLOAD_TIME,
       r.SOFT_TYPE,
       r.DOWNLOAD_IP,
       r.SERIAL_NO,
       l.LAN_NAME as LAN_NAME,(select soft_name
                               from diag_soft
                               where soft_id = r.soft_id) as SOFT_NAME
from DIAG_DOWNLOAD_RECORD  r          
  join SYS_LAN l on r.serial_no = '983290010120'  
    and r.soft_type = 2  and r.lan_id = l.lan_id
order by r.DOWNLOAD_TIME desc

-----------------------------------------------------------------
SQL> select * from table(sys.dbms_xplan.display_cursor('asdsn1yqzjk0r'))

SQL_ID  asdsn1yqzjk0r, child number 0
-------------------------------------
select r.DOWNLOAD_ID,r.VERSION_ID,r.SOFT_ID,r.VERSION_NO,r.VERSION_DETAI
L_ID,r.USER_ID,r.LAN_ID,r.DOWNLOAD_PERSON,r.DOWNLOAD_TIME,
r.SOFT_TYPE,r.DOWNLOAD_IP,r.SERIAL_NO,l.LAN_NAME as LAN_NAME,(select
soft_name from diag_soft where soft_id = r.soft_id) as SOFT_NAME from
DIAG_DOWNLOAD_RECORD  r          join SYS_LAN l on r.serial_no =:1 and
r.soft_type = 2          and r.lan_id = l.lan_id order by
r.DOWNLOAD_TIME desc

Plan hash value: 1686062289

------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                      |       |       | 17438 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID  | DIAG_SOFT            |     1 |    15 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN           | PK_DIAG_SOFT         |     1 |       |     0   (0)|          |
|   3 |  SORT ORDER BY                |                      |    16 |  1376 | 17438   (1)| 00:03:30 |
|   4 |   MERGE JOIN                  |                      |    16 |  1376 | 17437   (1)| 00:03:30 |
|   5 |    TABLE ACCESS BY INDEX ROWID| SYS_LAN              |     3 |    39 |     2   (0)| 00:00:01 |
|   6 |     INDEX FULL SCAN           | PK_SYS_LAN           |     3 |       |     1   (0)| 00:00:01 |
|*  7 |    SORT JOIN                  |                      |   110 |  8030 | 17435   (1)| 00:03:30 |
|*  8 |     TABLE ACCESS FULL         | DIAG_DOWNLOAD_RECORD |   110 |  8030 | 17434   (1)| 00:03:30 |
------------------------------------------------------------------------------------------------------

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

   2 - access("SOFT_ID"=:B1)
   7 - access("R"."LAN_ID"="L"."LAN_ID")
       filter("R"."LAN_ID"="L"."LAN_ID")
   8 - filter(("R"."SERIAL_NO"=:1 AND "R"."SOFT_TYPE"=2))


34 rows selected.


SQL> @/home/oracle/scripts/get_table_indexname
Enter value for tabn: DIAG_DOWNLOAD_RECORD
PK_DIAG_DOWNLOAD_RECORD        NORMAL                      UNIQUE    VALID    DOWNLOAD_ID
IND_DIAG_DOWNLOAD_RECORD       NORMAL                      NONUNIQUE VALID    LAN_ID

SQL> select count(*) from DIAG_DOWNLOAD_RECORD;

  COUNT(*)
----------
   3033458

SQL> desc DIAG_DOWNLOAD_RECORD
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 DOWNLOAD_ID                                                                                                       NOT NULL NUMBER(20)
 VERSION_ID                                                                                                                 NUMBER(20)
 SOFT_ID                                                                                                           NOT NULL NUMBER(20)
 VERSION_NO                                                                                                                 VARCHAR2(255)
 VERSION_DETAIL_ID                                                                                                          NUMBER(20)
 USER_ID                                                                                                                    NUMBER(20)
 LAN_ID                                                                                                                     NUMBER(20)
 DOWNLOAD_PERSON                                                                                                            VARCHAR2(255)
 DOWNLOAD_TIME                                                                                                              DATE
 SOFT_TYPE                                                                                                                  NUMBER(1)
 DOWNLOAD_IP                                                                                                                VARCHAR2(30)
 SOFT_NAME                                                                                                                  VARCHAR2(2048)
 SERIAL_NO                                                                                                                  VARCHAR2(20)

SQL>