Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  1. johnnybee

    Star Transformation is not happening

    Adding a Parallel hint instead of a Star Transformation gets the time down to 6 Seconds: /*+ PARALLEL (f_exp_det, 6) */ Explain Plan: Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop SELECT STATEMENT Optimizer Mode=ALL_ROWS 9 M 473792...
  2. johnnybee

    Star Transformation is not happening

    taupirho - changing the where clause as suggested made no difference. thargtheslayer - I didn't use autotrace, I used Explain Plan functionality within TOAD (not sure if that calls utlxpls). I deleted bits of the plan to make it more readable, I'll paste it in full below now: Operation Object...
  3. johnnybee

    Star Transformation is not happening

    I would have thought a combination of hitting the partitions and using the bitmap indexes was possible. If not, I'm open to suggestions as to how to tune this query. At the moment it takes 29 secs to return the 1st 100 rows. Not exactly light-speed!
  4. johnnybee

    Star Transformation is not happening

    ...joining to a larger dimension table to try and avoid the optimiser choosing to do a full table scan. This dimension has 15million+ rows. SELECT /*+ STAR_TRANSFORMATION */ f_exp_det.adjust_flag, f_exp_det.deal_id, f_exp_det.approved_approach_id...
  5. johnnybee

    Star Transformation is not happening

    I have the following query: SELECT /*+ STAR_TRANSFORMATION */ f_exp_det.adjust_flag, f_exp_det.deal_id, f_exp_det.approved_approach_id, f_exp_det.asset_class_id, d_asset_class.exposure_type, d_asset_class.exposure_type_desc...
  6. johnnybee

    SQL*Loader - Rejecting records, invalid number

    Tried with one record. When I removed all the special characters, it loads no problem. I don't think changing the fields to VARCHAR2 is going to fix the problem, since it seems the problem lies in the characters in the adjacent field.
  7. johnnybee

    SQL*Loader - Rejecting records, invalid number

    I'm in a windows environment. Actual setting for nls_lang is: american_america.WE8ISO8859P1 I tried UTF8 and WE8ISO8859P19, neither worked.
  8. johnnybee

    SQL*Loader - Rejecting records, invalid number

    ...VARCHAR2(255), EMEA_Vertical_Market_Sub_Group VARCHAR2(255), POS_Transaction_ID INTEGER, POS_Net_Price FLOAT) For this I am using SQL*Loader, the CTL file is defined as: load data infile 'c:\oracle\ora81\bin\imp_ecust.txt' append into table imp_ecust...
  9. johnnybee

    Help with tuning of a query

    Thats done the trick. Thank you and have a star :)
  10. johnnybee

    Help with tuning of a query

    Thanks for the reply, however, this does not quite solve the problem. What I am after is those with duplicate first_name & email address, regardless of contact_id. As you are grouping by both contact_id and first_name_email, your query will only pick out duplicates within contact_id...
  11. johnnybee

    Help with tuning of a query

    ...where there are duplicates on first name & email address (they can be different case, and have extra spaces). I have written this query: select * from (select c.contact_id,'same first_name||email' from cccadm.gcr_contacts c, cccadm.gcr_eaddresses e where c.contact_id = e.contact_id and...
  12. johnnybee

    Extra Spaces in Spool File

    This has indeed solved my problem. I can use the Convert function in the SQL and it does the trick. I will also talk to those with the necessary authority to look at changing the datatypes. Thank you all for your help :)
  13. johnnybee

    Extra Spaces in Spool File

    I posted the NLS parameters above. For NLS_LENGTH_SEMANTICS, it is CHAR.
  14. johnnybee

    Extra Spaces in Spool File

    I've already done DUMP(address_line_1) in an earlier post. However select length(address_line_1) from cccadm.gcr_postal_ss where contact_id = 15942373 Gives... LENGTH(ADDRESS_LINE_1) ---------------------- 19 Which confirms that ° is only one character. Not sure how...
  15. johnnybee

    Extra Spaces in Spool File

    Tried changing it anyway to AMERICAN_AMERICA.UTF8 , it seems to run OK, but still has the extra space.
  16. johnnybee

    Extra Spaces in Spool File

    I don't think you can set NLS_LANG environment variable to AMERICAN_AMERICA.UTF8. Here is a quote from this Oracle Documentation
  17. johnnybee

    Extra Spaces in Spool File

    No differences I am afraid, as the following shows: select dump(address_line_1) rom cccadm.gcr_postal_ss where contact_id = 15942373 DUMP(ADDRESS_LINE_1) -------------------- Typ=1 Len=20: 86,73,65,32,68,69,73,32,83,65,76,73,67,73,32,78,194,176,51,56 select dump('VIA DEI SALICI N°38') from...
  18. johnnybee

    Extra Spaces in Spool File

    I've checked the NLS_LANG environment variable in the registry and it is: AMERICAN_AMERICA.WE8ISO8859P1 This character set includes both of the unusual characters  and °, which means this is the correct setting. This means unfortunately my problem is not resolved.
  19. johnnybee

    Extra Spaces in Spool File

    ...different editors, including: Unipad, Textpad and Notepad. All seem to show the characters correctly (identically to how they are displayed in SQL*Plus), and all have the extra space. 2) I tried both changing the length of the columns and truncating them using "tru". Neither made any...
  20. johnnybee

    Extra Spaces in Spool File

    ...format a78 column COMPANY_NAME format a78 column COUNTY format a78 column NVL(STATE,COUNTRY) format a78 spool uk_contacts select * from (select a.CONTACT_ID, a.FIRST_NAME, a.LAST_NAME, a.JOB_TITLE, ADDRESS_LINE_1, ADDRESS_LINE_2, ADDRESS_LINE_3, ADDRESS_LINE_4, CITY, NVL(STATE,COUNTY)...

Part and Inventory Search

Back
Top