oracle

How to improve the performance of a query execution with bigger resultset in spring

I have a native query inside my Java Spring application that hits an oracle DB and fetches a large resultset (close to 20000 records). Current approach is that I used Spring’s JdbcTemplate to execute the query, which takes about few minutes to execute the query. What will be the better approach to improve the performance in executing this query? …
Read more

Updating multiple columns of one table at once

I want to know how many columns of a single table can be updated at one go ? Does it throw any performance issue ? If a table have 100 columns , how can i measure the peformnce ? Is it feasible to update 100 column at one go ? Source: oracle

Oracle performance: query executing multiple identical function calls

Is it possible for Oracle to reuse the result of a function when it is called in the same query (transaction?) without the use of the function result cache? The application I am working with is heavily reliant on Oracle functions. Many queries end up executing the exact same functions multiple times. A typical example would be: SELECT my_package.my_function(my_id), my_package.my_function(my_id)…
Read more

How to delete a Trigger?

I create some Triggers like the following for example: CREATE OR REPLACE TRIGGER TRIGGER_USER BEFORE INSERT ON HOUSE_USER REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW WHEN (NEW.USER_ID IS NULL) BEGIN SELECT SEQ_USER_ID.NEXTVAL INTO :NEW.USER_ID FROM DUAL; END; / But i cant drop them. When i try to drop it: DROP TRIGGER TRIGGER_USER; I get the following error:…
Read more

how to run an application using oracle

everybody. I have an application which is running on server. Every second my application checks inserted rows in the table . After row was inserted application sends some necessary info to the emails. So, connection to DB (means a session) effects to the system performance. It takes a lot of memory. My question is next. Is it possible to implement…
Read more

Leave column out of Oracle insert statement

I have an insert statement that I am trying to run against an Oracle database. The insert statement is very simple, and I only want to insert a value into one column. The table itself has more than one column, but I am only interested in filling one of the columns with data. The format of my query is similar…
Read more

Deleting by ROWID

I’d like to run this query. DELETE FROM A006873.GC_CLIENTS WHERE ROWID = ‘14519’; But I get this error: SQL.sql: Error (2,15): ORA-01410: invalid ROWID I got in GC_CLIENTS ID NUMBER NAME 14519 0000017690 VILLAVINE I don’t realize what’s wrong if I’m in the correct ID. I’m using a sequence by the way and I don’t want to delete it with…
Read more