Jump to content

Arunachalam.S

Members
  • Content count

    9
  • Joined

  • Last visited

Community Reputation

0 Neutral

About Arunachalam.S

  • Rank
    Newbie

Recent Profile Visitors

The recent visitors block is disabled and is not being shown to other users.

  1. Arunachalam.S

    Sql tuning

    Hi Please tell me how to tune the below query. Attached the explain plan for reference. Also please note the object names po, prline, rfq, rfqline are views. These views are having two select statements by using union all. The first select statement refers another schema table of same database and another select refers table from another database by using database link. The scenario is like this... I'm archiving old data into another database and current database has only few records to improve the performance of application. There are some users which required to generate reports by using two database (Production and Archived database.) So the view is created like this.. Create or replace view schema2.v1 select col1,col2 from schema1.table1 union all select col1, col2 from schema.table1@dblink; The above scenario is explained for IBM Maximo application and we are using IBM OPTIM for archiving. Also the maximo is generating the sql and sending to database like below queries. SELECT Count(*) FROM dummy_table WHERE EXISTS(SELECT 1 FROM po WHERE ponum IN (SELECT ponum FROM prline WHERE ponum IS NOT NULL AND prnum = 'D-8178') OR ponum IN (SELECT ponum FROM rfqline WHERE rfqnum IN (SELECT rfqnum FROM prline WHERE prnum = 'D-8178'))) OR EXISTS(SELECT 1 FROM rfq WHERE rfqnum IN (SELECT rfqnum FROM prline WHERE prnum = 'D-8178')); Please help me out how to overcome these kind of problems. Explain_plan.html
  2. Arunachalam.S

    Status column update for Duplicate row

    Hi Burleson, Thanks for the reply. Please note that Status output is dynamically achieved. If there is only one employee in the org_table the status value would be C . If there is more records for the same employee the minimum create date is C and others should be U. Kindly give me the query for the above scenario. I will be very thankfull to you.
  3. Arunachalam.S

    Status column update for Duplicate row

    Hi, I have one scenario like the following. ORG_TABLE : EMP_ID TITLE CREATED_DATE -------------------------------------- 100 B.Sc 04/04/2010 101 MTech 05/04/2009 102 MBA 06/04/2012 100 M.Sc 04/04/2013 EMP_Table: EMP_ID -------- 100 101 102 output : EMP_ID TITLE CREATED_DATE STATUS 100 B.Sc 04/04/2010 C 100 M.Sc 04/04/2013 U 101 MTech 05/04/2009 C 102 MBA 06/04/2012 C I need a query to bring the above output.
  4. Arunachalam.S

    How to Find a New records in a view

    Hi , I have one view and I want to find a new records from the view. But the view is given by the third party. For example : emp_vw is a view which is give from some other database with different IP. I have access for read only in that view. I need to get only the new records from view. How to get that ? I know A-B will work but what happens if it has millions of records in the view. Does it gives better performance? Please give me in performance wise result. Also tell me how to get the above result by using Job. Thanks.
  5. Arunachalam.S

    Does Rowid change when trigger fires

    Hi, IN ORACLE11G WHEN I DO INSERT STATEMENT THE ASSOCIATED TRIGGER WILL GET FIRED AND I DO INSERT THE ROWID INTO AUDIT TABLE. BUT THE ROWID IS GETTING DIFFERENT. PLEASE REFER THE BELOW CODE. TABLE 1 : ======== CREATE TABLE ARUN123 ( A NUMBER, B NUMBER ); TABLE 2: ====== CREATE TABLE ARUN ( AB NUMBER, BC NUMBER, ARUNROW VARCHAR2(255) ); TRIGGER: ======= CREATE OR REPLACE TRIGGER arun_TGR AFTER DELETE OR UPDATE or INSERT ON Arun123 FOR EACH ROW BEGIN IF INSERTING THEN BEGIN INSERT INTO ARUN VALUES (:NEW.A, :NEW.B, :NEW.ROWID ); END; END IF; END; iNSERT STMT: ========= INSERT INTO ARUN123 VALUES(1,2); WHEN I DO SELECT FOR ARUN123 TABLE I GOT THE BELOW RESULT SELECT ROWID FROM ARUN123; AAApk1AAAAADdAVAAA SELECT * FROM ARUN; AAApk1AQAAADdAVAAA HERE MY ISSUE IS ROWID IS DIFFERENT IN ORACLE 11G. PLEASE SOMEBODY HELP IN THIS REGARD.
  6. Arunachalam.S

    Who Compile my Stored Procedure

    Hi Frndz., I have created one SP called abc .. some of my neighbours are accessing my SP and re-modifying and compiling it. I want to find who compiled my SP at last. I want to know from which machine and which user. Please do this help for me.
  7. Arunachalam.S

    Simulation between database

    thanks for the reply burleson. can you please provide any links for the steps to build for replication. Is that possible to have replication on two different servers. because database1 is in one server and database2 in another server
  8. Arunachalam.S

    Simulation between database

    Hi Frnds, I have two databases database1(System Testing) and database2 (Unit testing). Both having the same user name "arun". what my question is i want a simulation of databse1 to database2. For eg., if inserting or deleting or updating any table in database1 the same should be reflect database2. Is that possible ? If yes how ? Please help me...
  9. Arunachalam.S

    Single to Multiple Records

    Hi Krish, your answer is here. create table sample ( a number, b varchar2(50) ); insert into sample values(1,'abcdefghijkl'); select a,lpad(b,10) from sample union select a,replace(b,lpad(b,10)) from sample;
×