Ask the Experts

Ask the Experts

Oracle and SQL

  • Temp space usage increases when query includes view

    I run a query which joins four to five tables (it includes views). When the query is run without a particular view the query runs fine, but when we include that view the temp space usage by the query increases rapidly.  Continue Reading

  • Execution time increases with packaged code

    I've created a package that moves data from one schema to another. Within the stored procs are insert statments that move the data. If I copy the data to the target schema and then run the insert statements, they execute much faster than moving the ...  Continue Reading

  • What session is consuming the tablespace?

    My users are filling up the UNDO tablespace and TEMP tablespace whenever they run a process which takes up to approximately six hours. This process spans several sessions (from WebLogic Server), uses a lot of global temp tables, types, etc., from ...  Continue Reading

  • Package slow after upgrade

    Our database was recently upgraded from Oracle 8.0.6 to, just before I started to work here. They have a package with a maximum of 32 input report parameters that was working very well under the 8.0.6 version and that is now extremely slow,...  Continue Reading

  • Deleting Parent and Child rows

    I have two tables (Parent and Child) containing millions of records. I want to delete some rows (106 rows) from both tables. Which is the best approach to do this? I have dropped a foreign key constraint before deleting rows from Parent. But when...  Continue Reading

  • Defining object classes in C++

    Can I define object classes and tables from the object classes for use with OCCI in C++, which contain character strings, binary integers and binary floating point numbers, and which will be initialized and saved from the C++ program, but can also ...  Continue Reading

  • Slow "create table" statement

    I have two servers; one is Oracle on Unix, the other is Oracle on Windows. I tried this SQL command: "create table xx as select * from table1@server2." This statement is very slow (43 seconds), but the query is very fast (0 to 1 ...  Continue Reading

  • Improving performance of inserts

    First we read records from three different tables and insert into a temp table. From the temp table I fetch the records one by one, do some calculations and insert into another table if the record is new. If it already exists then I update the ...  Continue Reading

  • Slow for loop cursor

    I have three tables, A, B and C. Table A has 450,000 records, and B has 80,000. I have created a for loop cursor on table A and check B in that loop. If a record is found in B then I want it to be inserted in table C. But I waited eight hours for ...  Continue Reading