| Can You Help me Creating index for the Given Problem [message #57678] |
Mon, 30 June 2003 00:43  |
NEPAL
Messages: 8
Registered: March 2003
|
Junior Member |
|
|
Hello,
I have a problem creating index for the tables/queries that I am giving you here to execute the Data quickly....
=====================
PROCEDURE Display_List IS
last_row INTEGER := 0;
last_col INTEGER := 0;
vEmployeeId VARCHAR2(6);
vPayrollItemCode VARCHAR2(2);
nAmount NUMBER(11,2);
vMonthYr VARCHAR2(10);
dEffectiveDate DATE;
CURSOR CurEmployee IS /* Cursor 1 */
SELECT E.EMPLOYEE_ID
FROM JANAK.EMPLOYEE_INFORMATION E, JANAK.EMPLOYEE_OFFICIAL_HISTORY V
WHERE NVL(CURRENTLY_ON_JOB,'N') = 'Y'
AND E.EMPLOYEE_ID = V.EMPLOYEE_ID
AND E.EMPLOYEE_ID = NVL(:CONTROL_BLOCK.EMPLOYEE_ID,E.EMPLOYEE_ID)
AND V.ORG_STRUCTURE_CODE IN
(SELECT ORG_STRUCTURE_CODE FROM JANAK.ORG_STRUCTURE_HIERARCHY
CONNECT BY PRIOR ORG_STRUCTURE_CODE = UPPER_ORG_STRUCTURE_CODE
START WITH ORG_STRUCTURE_CODE = NVL(:CONTROL_BLOCK.ORG_STRUCTURE_CODE, V.ORG_STRUCTURE_CODE)) AND
V.EFFECTIVE_DATE_FROM = (SELECT MAX(EFFECTIVE_DATE_FROM)
FROM JANAK.EMPLOYEE_OFFICIAL_HISTORY WHERE
EMPLOYEE_ID = E.EMPLOYEE_ID AND EFFECTIVE_DATE_FROM <= dEffectiveDate)
ORDER BY E.EMPLOYEE_ID;
CURSOR CurPayroll IS /* Cursor 2 */
SELECT DISTINCT P.PAYROLL_ITEM_CODE , P.PAYROLL_ITEM_TYPE,
P.SERIAL_NO FROM JANAK.PAYROLL_ITEM_CODE P,
JANAK.VW_MONTHLY_SALARY_HISTORY M
WHERE P.PAYROLL_ITEM_CODE = M.PAYROLL_ITEM_CODE
AND M.PAYROLL_MONTH = vMonthYr
ORDER BY P.PAYROLL_ITEM_TYPE , P.SERIAL_NO;
CURSOR CurSalary IS /* Cursor 3 */
SELECT AMOUNT FROM JANAK.VW_MONTHLY_SALARY_HISTORY
WHERE PAYROLL_MONTH = vMonthYr AND
EMPLOYEE_ID = vEmployeeId AND
PAYROLL_ITEM_CODE = vPayrollItemCode;
BEGIN
IF :GLOBAL.CalendarType = 'AD' THEN
vMonthYr := :CTRL_MONTH ||:CTRL_YEAR;
dEffectiveDate := LAST_DAY(TO_DATE(vMonthYr,'MONYYYY'));
ELSE
vMonthYr := :CTRL_MONTH ||:CTRL_YEAR;
dEffectiveDate := JANAK.CALENDAR.BSTOAD(SUBSTR(vMonthYr, 3,4) || '-' || SUBSTR(vMonthYr,1,2) || '-' || JANAK.CALENDAR.GET_DAYS_IN_BSMONTH(vMonthYr));
END IF;
HIDE_WINDOW('WINDOW2');
HIDE_VIEW('CAN_INITIAL');
SET_WINDOW_PROPERTY('WINDOW1', WINDOW_STATE, MAXIMIZE);
SHOW_VIEW('CAN_EMP');
SHOW_VIEW('CAN_SAL');
GO_BLOCK('EMPLOYEE_INFORMATION');
FIRST_RECORD;
FOR c IN CurEmployee LOOP
:EMPLOYEE_INFORMATION.EMPLOYEE_ID := c.EMPLOYEE_ID;
last_row := last_row + 1;
:EMPLOYEE_INFORMATION.EMPLOYEE_NAME := JANAK.PERSONNEL.EMPLOYEE_NAME(:EMPLOYEE_INFORMATION.EMPLOYEE_ID);
:EMPLOYEE_POSITION := JANAK.PERSONNEL.POSITION_NAME(JANAK.PERSONNEL.EMPLOYEE_STATUS(:EMPLOYEE_INFORMATION.EMPLOYEE_ID,'P',SYSDATE));
NEXT_RECORD;
END LOOP;
GO_BLOCK('MONTHLY_SALARY');
FIRST_RECORD;
FOR a IN CurPayroll LOOP
:MONTHLY_SALARY.PAYROLL_ITEM_CODE := a.PAYROLL_ITEM_CODE;
last_col := last_col + 1;
NEXT_RECORD;
END LOOP;
FOR rec_no IN 1..last_row LOOP
GO_BLOCK('EMPLOYEE_INFORMATION');
GO_RECORD(rec_no);
vEmployeeId := :EMPLOYEE_INFORMATION.EMPLOYEE_ID;
FOR col_no IN 1..last_col LOOP
GO_BLOCK('MONTHLY_SALARY');
GO_RECORD(col_no);
vPayrollITemCode := :PAYROLL_ITEM_CODE;
nAmount := 0;
OPEN CurSalary;
FETCH CurSalary INTO nAmount;
CLOSE CurSalary;
COPY(nAmount, 'AMOUNT' || TO_CHAR(col_no));
END LOOP;
END LOOP;
GO_BLOCK('EMPLOYEE_INFORMATION');
FIRST_RECORD;
GO_ITEM('BTN_BACK2');
END;
===============
Well, Now, I need the output from this Procedure quickly since it is retrieving data so slowly even for about 329 records.........
I hope to get your help.................
===============================
I have given you some table structures here and if you need more structures, you can mail me at jyapu@hotmail.com
Thanking you....
Yours,
Roshan
Here is some table structures..........
===================
SQL> desc employee_information
Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID NOT NULL VARCHAR2(6)
FIRST_NAME VARCHAR2(15)
MIDDLE_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
EMPLOYEE_NAME_NEPALI VARCHAR2(50)
DATE_OF_BIRTH DATE
PLACE_OF_BIRTH VARCHAR2(3)
GENDER CHAR(1)
MARITAL_STATUS CHAR(1)
RELIGION_CODE VARCHAR2(2)
NATIONALITY_CODE VARCHAR2(2)
PERMANENT_ADDRESS VARCHAR2(100)
PER_DISTRICT VARCHAR2(30)
PER_COUNTRY_CODE VARCHAR2(2)
TEMPORARY_ADDRESS VARCHAR2(100)
TEMP_DISTRICT VARCHAR2(30)
TEMP_COUNTRY_CODE VARCHAR2(2)
CONTACT_PHONE_NO VARCHAR2(30)
APPOINTED_ON DATE
APPOINTED_AGE NUMBER(2)
APPOINTED_BY VARCHAR2(40)
JOINED_ON DATE
CONTRACT_EXPIRES_ON DATE
PROBATION_END_DATE DATE
BANK_ACCOUNT_NO VARCHAR2(15)
NAGRIK_LAGANI_KOSH VARCHAR2(10)
PF_NO VARCHAR2(10)
CURRENTLY_ON_JOB CHAR(1)
CITIZENSHIP_NO VARCHAR2(20)
ISSUED_ON DATE
ISSUED_PLACE VARCHAR2(3)
HEIGHT VARCHAR2(5)
WEIGHT VARCHAR2(5)
COMPLEXION VARCHAR2(20)
EYE_COLOR VARCHAR2(10)
HAIR_COLOR VARCHAR2(10)
IDENTIFICATION_MARK VARCHAR2(20)
DESCRIPTION VARCHAR2(100)
(Primary key used in this table PK_EMP_ID)
SQL> desc JANAK.ORG_STRUCTURE_HIERARCHY
Name Null? Type
------------------------------- -------- ----
ORG_STRUCTURE_CODE NOT NULL VARCHAR2(3) PK_ORGH_CODE is Primary Key.
ORG_STRUCTURE_NAME VARCHAR2(40)
ORG_NAME_NEPALI VARCHAR2(50)
DESCRIPTION VARCHAR2(100)
HIERARCHY_CODE VARCHAR2(3)
UPPER_ORG_STRUCTURE_CODE VARCHAR2(3)
LOCATION_CODE VARCHAR2(3)
SQL> desc EMPLOYEE_OFFICIAL_HISTORY
Name Null? Type
------------------------------- -------- ----
EMPLOYEE_ID VARCHAR2(6)
HISTORY_CHANGE_TYPE CHAR(1)
POSITION_CODE VARCHAR2(3)
ORG_STRUCTURE_CODE VARCHAR2(3)
JOB_CODE VARCHAR2(3)
JOB_STATUS_CODE VARCHAR2(3)
CURRENT_STEP NUMBER(2)
SHIFT_CODE VARCHAR2(1)
EFFECTIVE_DATE_FROM DATE
EFFECTIVE_DATE_TO DATE
JOINED_DATE DATE
APPROVED_DATE DATE
APPROVED_BY VARCHAR2(6)
REMARKS VARCHAR2(200)
APPOINTED_TAG VARCHAR2(1)
PROMOTION_TAG VARCHAR2(1)
TRANSFER_TAG VARCHAR2(1)
DEPUTED_TAG VARCHAR2(1)
JOB_TAG VARCHAR2(1)
JOBSTATUS_TAG VARCHAR2(1)
SHIFT_TAG VARCHAR2(1)
PROMOTION_STATUS VARCHAR2(1)
DEMOTION_TAG VARCHAR2(1)
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
